This formula returns TRUE if the substring is found, and FALSE if not. Note the SEARCH function is not case-sensitive. See below for a case-sensitive formula.

SEARCH function (not case-sensitive)

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: To force a TRUE or FALSE result, we use the ISNUMBER function. ISNUMBER returns TRUE for numeric values and FALSE for anything else. So, 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, which causes the ISNUMBER to return FALSE.

Wildcards

Although SEARCH is not case-sensitive, it does support wildcards (*?~). The formula below tests for a text value 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”:

FIND function (case-sensitive)

Like the SEARCH function, the FIND function returns the position of a substring in text as a number, and an error if the substring is not found. However, unlike the SEARCH function, the FIND function respects case: To make a case-sensitive version of the formula, just replace the SEARCH function with the FIND function in the formula above: The result is a case-sensitive search:

If cell contains

To return a custom result when a cell contains specific text, add 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.

With hardcoded search string

To test for a hardcoded substring, enclose the text in double quotes (""). For example, to check A1 for the text “apple” use:

More than one search string

To test a cell for more than one thing (i.e. for one of many substrings), see this example formula.

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.