## Explanation

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

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