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: 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: 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: To start searching at character 4, enter 4 for start_num:
Wildcards
Although SEARCH is not case-sensitive, it does support wildcards (*?~). The formula below looks for a substring that ends with “z”: The wildcard ? matches in one character. The formula below looks for a 3 character substring beginning with “x” and ending in “y”:
If cell contains
To return a custom result with the SEARCH function, use the IF function like this: 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 ~?.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
