Cell contains specific text

In this example, the goal is to test a value in a cell to see if it contains a specific substring. Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. Both functions return the position of the substring if found as a number, and a #VALUE! error if the substring is not found. The difference is that the SEARCH function supports wildcards but is not case-sensitive, while the FIND function is case-sensitive but does not support wildcards. The general approach with either function is to use the ISNUMBER function to check for a numeric result (a match) and return TRUE or FALSE.

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 the 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!

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:

=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE

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 (*?~). 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 matches zero or more characters. This 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":

=ISNUMBER(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

This means the asterisk (*) is not a reliable way to test for "ends with". However, you can 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, you can 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.

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:

=FIND("A","Apple") // returns 1
=FIND("A","apple") // returns #VALUE!

To make a case-sensitive version of the formula, just replace the SEARCH function with the FIND function in the formula above:

=ISNUMBER(FIND(substring,A1))

The result is a case-sensitive search:

=ISNUMBER(FIND("A","Apple")) // returns TRUE
=ISNUMBER(FIND("A","apple")) // returns FALSE

If cell contains

To return a custom result when a cell contains specific text, add 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 the substring is found and "No" if not.

With a 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:

=ISNUMBER(SEARCH("apple",A1))

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.