If cell contains

The goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns "x" when a cell contains "abc". If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself to test for a substring like "abc" that might appear anywhere in a cell. One solution is to create a logical test with the SEARCH and ISNUMBER functions, then use the IF function to return a final result. This approach is explained below.

ISNUMBER + SEARCH

The SEARCH function is designed to look inside a text string for a given 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!

The ISNUMBER function returns TRUE for numeric values and FALSE for anything else:

=ISNUMBER(2) // returns TRUE
=ISNUMBER("a") // returns FALSE
=ISNUMBER(#VALUE!) // returns FALSE

We can use ISNUMBER to convert the result from the SEARCH function into a TRUE or FALSE value:

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

To recap: 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.

IF function

We can use the combination of SEARCH + ISNUMBER as a logical test inside the IF function. In the worksheet shown, we have a list of email addresses, and we want to identify those that contain "abc". The formula in cell C5 is:

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

If "abc" is found anywhere in cell B5, SEARCH returns a number and ISNUMBER returns TRUE. The IF function then returns "x" as a final result. If "abc" is not found, SEARCH returns an error and ISNUMBER returns FALSE. The IF function then returns an empty string ("") as a final result. 

Return matching values

With a small adjustment, we can return the value that contains "abc" instead of returning "x":

To return a cell of the value when it contains "abc", we provide a reference for the value if true argument. If FALSE, we supply an empty string ("") which will display as a blank cell. The formula in cell C5 is:

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

Notes

1. The SEARCH function is not case-sensitive. If you need a case-sensitive option you can switch to the FIND function as explained here.
2. If the goal is to collect all matching cells or records together, see the FILTER function.