When working with messy data dumps in Microsoft Excel, you often need to locate a specific word or character hidden deep inside a long string of text. For example, you might need to find exactly where the “@” symbol is located in a list of email addresses so you can extract the domain name.
To find the numeric position of a character, you use the SEARCH function.
The Syntax of the SEARCH Function
The SEARCH function looks for a piece of text inside a larger piece of text, and tells you exactly what character number it starts at.
Syntax: =SEARCH(find_text, within_text, [start_num])
- find_text: The specific word, letter, or symbol you are looking for (always wrap this in double quotes).
- within_text: The cell containing the full text you are searching through.
- start_num (optional): Where in the string you want to start looking. If you leave this blank, it defaults to 1 (the beginning).
A Practical Example
Imagine Cell A2 contains the text string: “Invoice-5942-Pending”.
You want to find exactly where the word “Pending” starts so you can use other formulas to slice it out.
You type: =SEARCH("Pending", A2)
Excel will output the number 14. This is because the letter “P” in “Pending” is the 14th character in the string (counting the letters and the hyphens).
SEARCH vs. FIND: The Critical Difference
Excel actually has two functions that do the exact same job: SEARCH and FIND. They use the exact same syntax, but they have one massive difference.
- SEARCH is Case-Insensitive: It does not care about capital letters. If you ask
=SEARCH("apple", "RED APPLE"), it will successfully find the word. - FIND is Case-Sensitive: It requires a perfect match. If you ask
=FIND("apple", "RED APPLE"), Excel will throw a#VALUE!error because “apple” does not perfectly match “APPLE”.
Furthermore, the SEARCH function allows you to use wildcard characters like an asterisk (*) or question mark (?) in your search text, while the FIND function does not.
As a general rule, you should always use SEARCH unless you specifically require strict, case-sensitive matching.