=ISNUMBER(SEARCH(substring,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.

Copy cells that contains specific text

If you want to copy cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. Once you find a value you're looking for you can copy it to another location, or display a message, or perform some other calculation.

If cell contains "abc", copy it elsewhere:

In the example shown, we have a list of email addresses, and we want to copy those that contain "abc". In C5, the formula were using is this:

=IF(ISNUMBER(SEARCH("abc",B5)),B5,"")

In this formula, the logical test is this bit:

ISNUMBER(SEARCH("abc",B5))

This will return TRUE if the the value in B5 contains "abc" and false if not.

To copy cell B5 if TRUE, we just need to supply B5 again for the "value if true" argument. If false, we supply an empty string ("") which will display as a blank cell on the worksheet.