## Explanation

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 a *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 (*?~). The formula below tests for a text value that ends with "z":

```
=ISNUMBER(SEARCH("*z","XYZ")) // TRUE
=ISNUMBER(SEARCH("*z","XYY")) // FALSE
```

The wildcard ? matches in 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
```

### 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 *substring* is found and "No" if not.

### With 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.