Excel SEARCH Function
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
- find_text - The text to find.
- within_text - The text to search within.
- start_num - [optional] Starting position in the text to search. Optional, defaults to 1.
The SEARCH function returns the position (as a number) of one text string inside another. If there is more than one occurence of the search string, SEARCH returns the position of the first occurrence. SEARCH is not case-sensitive, but does support wildcards. Use the FIND function to perform a case-sensitive find. When SEARCH doesn not find anything, it returns a #VALUE error. Also note that when find_text is empty, SEARCH will return 1. This can cause a false positive when find_text comes from a cell, and the cell is empty.
To return the position of the first "a" in the word "Apple":
=SEARCH("a","Apple") // returns 1
Note hardcoded text values must be enclosed in double quotes ("").
To find the first match of "the" in any combination of upper or lowercase:
=SEARCH("the","The cat in the hat") // returns 1
Same search, starting at character 4:
=SEARCH("the","The cat in the hat",4) // returns 12
=SEARCH("?at","The cat in the hat") // returns 5
Error if not found:
=SEARCH("dog","The cat in the hat") // returns #VALUE!
See below for examples of search in more advanced formulas.
- SEARCH returns the position of the first find_text in within_text.
- Start_num is optional and defaults to 1.
- Use the FIND function for a case-sensitive search.
- SEARCH allows the wildcard characters question mark (?) and asterisk (*), in find_text.
- ? matches any single character and
- * matches any sequence of characters.
- To find a literal ? or *, use a tilde (~) before the character, i.e. ~* and ~?.