Cell contains specific text

If you need to check if a cell contains a substring (specific text), you can  do so with the SEARCH function together with the ISNUMBER function. In the generic form of the formula (above), substring is the specific text you are testing for, and text represents text in the cell you are checking.

You can use this formula to find cells that contain one word, or part of a word.

How the formula works

The SEARCH function returns the position of substring when it’s found in text, and the #VALUE! error if not. The ISNUMBER function returns TRUE for numbers and FALSE for anything else. So, if SEARCH finds substring, it returns the position as a number, and ISNUMBER returns TRUE. However, if SEARCH doesn’t find substring, it returns a #VALUE! error, causing ISNUMBER to return FALSE.

If you want this formula to be case-sensitive, you can replace the SEARCH function with the FIND function like so:

 
=ISNUMBER(FIND(substring,text))

If you want to do something more than just test whether a cell contains specific text, you can wrap the formula in an IF statement like this:
 
=IF(ISNUMBER(SEARCH(substring,text)), "Yes", "No")

Instead of returning TRUE or FALSE, the formula above, will return “Yes” if substring is found and “No” if not.
Looking for more than one thing?
If you need to test a cell to see it contains one of many things, see this formula.