Purpose
Return value
Arguments
- find_text - The substring to find.
- within_text - The text to search within.
- start_num - [optional] Starting position. Optional, defaults to 1.
Syntax
How to use
The SEARCH function returns the position (as a number) of one text string inside another. If there is more than one occurrence 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 does not find anything, it returns a #VALUE! error. Note that when find_text is empty, SEARCH will return 1. This can cause a false positive when find_text is an empty cell.
Basic Example
The SEARCH function is designed to look inside a text string for a specific substring. If SEARCH finds the substring, it returns a position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:
=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!
Note that text values entered directly into SEARCH must be enclosed in double-quotes ("").
TRUE or FALSE result
To force a TRUE or FALSE result, nest SEARCH inside the ISNUMBER function. ISNUMBER returns TRUE for numbers and FALSE for anything else. If SEARCH finds the substring, it returns the position as a number, and ISNUMBER returns TRUE:
=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE
If SEARCH doesn't find the substring, it returns an error, and ISNUMBER returns FALSE.
Start number
The SEARCH function has an optional argument called start_num, that controls where SEARCH should begin looking for a substring. To find the first match of "the", you can omit start_num, which defaults to 1:
=SEARCH("the","The cat in the hat") // returns 1
To start searching at character 4, enter 4 for start_num:
=SEARCH("the","The cat in the hat",4) // returns 12
Wildcards
Although SEARCH is not case-sensitive, it does support wildcards (*?~). For example, the question mark (?) wildcard matches any one character. The formula below looks for a 3-character substring beginning with "x" and ending in "y":
=ISNUMBER(SEARCH("x?z","xyz")) // TRUE
=ISNUMBER(SEARCH("x?z","xbz")) // TRUE
=ISNUMBER(SEARCH("x?z","xyy")) // FALSE
The asterisk (*) wildcard is not as useful in the SEARCH function because SEARCH already looks for a substring. For example, it might seem like the following formula will test for a value that ends with "z":
=SEARCH("*z",text)
However, because SEARCH automatically looks for a substring, the following formulas all return 1 as a result, even though the text in the first formula is the only text that ends with "z":
=SEARCH("*z","XYZ") // returns 1
=SEARCH("*z","XYZXY") // returns 1
=SEARCH("*z","XYZXY123") // returns 1
=SEARCH("x*z","XYZXY123") // returns 1
However, it is possible to use the asterisk (*) wildcard like this:
=SEARCH("x*2*b","AAAXYZ123ABCZZZ") // returns 4
=SEARCH("x*2*b","NXYZ12563JKLB") // returns 2
Here we are looking for "x", "2", and "b" in that order, with any number of characters in between. Finally, use the tilde (~) as an escape character to indicate that the next character is a literal like this:
=SEARCH("~*","apple*") // returns 6
=SEARCH("~?","apple?") // returns 6
=SEARCH("~~","apple~") // returns 6
The above formulas use SEARCH to find a literal asterisk (*), question mark (?) , and tilde (~) in that order.
If cell contains
To return a custom result with the SEARCH function, use the IF function like this:
=IF(ISNUMBER(SEARCH(substring,A1)), "Yes", "No")
Instead of returning TRUE or FALSE, the formula above will return "Yes" if substring is found and "No" if not.
Notes
- 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 ~?.