## Purpose

## Return value

## Syntax

`=SEARCH(find_text,within_text,[start_num])`

*find_text*- The substring to find.*within_text*- The text to search within.*start_num*- [optional] Starting position. Optional, defaults to 1.

## How to use

The SEARCH function returns the position (as a number) of one text string inside another. In the most basic case, you can use SEARCH to locate the position of a substring in a text string. You can also use SEARCH to check if a cell contains specific text. SEARCH is *not case-sensitive*, which means it does not distinguish between uppercase and lowercase letters. In addition, SEARCH supports the use of wildcards like *?~, allowing more flexible search patterns. Here are a few key points to remember about the SEARCH function:

- SEARCH returns the
*position*of one text string inside another as a*number*. - When SEARCH cannot locate the search string, it returns a #VALUE error.
- If the search string appears more than once, SEARCH returns the
*first position*. - SEARCH is
*not case-sensitive*and will treat "Apple" and "apple" as the same text strings. - SEARCH
*does*support wildcards like *?~ when searching for text. - SEARCH will return 1 if the search string (
*find_text*) is empty, which can cause a false positive when*find_text*is an empty cell.

*Note: The SEARCH function is similar to the FIND function. Both functions return the position of one text string inside another. However, unlike FIND, SEARCH is not case-sensitive and *does* support wildcards.*

### Basic syntax

The basic syntax of the SEARCH function looks like this

`SEARCH(find_text,within_text,[start_num])`

*find_text*: The text you want to find (the search string). This is a substring that Excel searches for within another text string. The text must be entered in double quotes if you are hardcoding the value into the formula. Otherwise, you can refer to a cell that contains the text.*within_text*: The text string that contains the text you want to find. Often this is a cell reference that contains the text, but you can also hardcode a text string in double quotes.*start_num*(optional): The character at which to begin searching, as a numeric position. The first character in*within_text*is considered position 1. If omitted, the search starts at the beginning of the*within_text*.

### Basic example

The SEARCH function is designed to look inside a text string for a specific substring. When SEARCH locates 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!
=SEARCH("apple","Pineapple") // returns 5
```

Note that text values entered directly into SEARCH must be enclosed in double quotes (""). Unlike the FIND function, the SEARCH function is *not* case-sensitive:

```
=SEARCH("a","Apple") // returns 1
=SEARCH("A","Apple") // returns 1
=SEARCH("Apple","Pineapple") // returns 5
```

The worksheet below shows the same examples translated into formulas based on cell references:

Again, notice that SEARCH is not case-sensitive as seen in D8 and D10.

### Forcing a TRUE or FALSE result

By default, the SEARCH function returns a number when a search string is found and a #VALUE! error when not. This is inconvenient in cases where you simply want to know if the search string has been found or not. To force a TRUE or FALSE result, you can nest the SEARCH function inside the ISNUMBER function. ISNUMBER returns TRUE for numeric values and FALSE for anything else. If SEARCH locates 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* locate the substring, it returns an error, and ISNUMBER returns FALSE. This approach allows for support for wildcards in the search. For a more detailed explanation of this approach, with many more examples, see this example.

### If cell contains

Once you have a TRUE or FALSE result, you can combine the SEARCH function with the IF function to create "if cell contains" logic. The generic pattern for this formula looks like this:

```
=IF(ISNUMBER(SEARCH(substring,A1)), "Yes", "No")
```

Instead of returning TRUE or FALSE, the formula above will return "Yes" if the *substring* is found and "No" if not. You can use the same idea to mark or "flag" items of interest. For example, in the worksheet below, we are using SEARCH with IF to flag email addresses that contain "abc" with an "x".

The formula in C5, copied down, is:

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

### Start number

The SEARCH function has an optional argument called *start_num*, that controls where SEARCH should begin looking for a substring. To find the first match of "x" in upper or lowercase, you can omit *start_num*, which defaults to 1:

`=SEARCH("x","20 x 30 x 50") // returns 4`

SEARCH returns 4 since the first "x" appears at position 4. To find the second "x", enter 5 for *start_num*:

```
=SEARCH("x","20 x 30 x 50",5) // returns 9
```

In this case, SEARCH returns 9 since it starts searching after the first "x". You can effectively find the second "x" in cell A1 in a single formula by using the SEARCH function twice like this:

`=SEARCH(A1,SEARCH("x",A1)+1)`

The *inner* SEARCH returns the location of the first "x". We then add 1 and the result is used as the *start_num* in the *outer* SEARCH. The result is the location of the second "x" in cell A1.

### Wildcards

Although SEARCH is *not* case-sensitive, it does support wildcards (*?~), which makes it a versatile tool for finding substrings in text. This allows for more complex searches, such as those including basic pattern matching. For example, the question mark (?) wildcard matches any 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
```

The asterisk (*) wildcard is not as useful in the SEARCH function because SEARCH *already looks for a substring*. For example, it might seem like the following formula will test for a value that ends with "z":

`=SEARCH("*z",text)`

However, because SEARCH *automatically looks for a substring*, the following formulas all return 1 as a result, even though the text in the first formula is the only text that ends with "z":

```
=SEARCH("*z","XYZ") // returns 1
=SEARCH("*z","XYZXY") // returns 1
=SEARCH("*z","XYZXY123") // returns 1
=SEARCH("x*z","XYZXY123") // returns 1
```

However, it is possible to use the asterisk (*) wildcard like this:

```
=SEARCH("x*2*b","AAAXYZ123ABCZZZ") // returns 4
=SEARCH("x*2*b","NXYZ12563JKLB") // returns 2
```

Here we are looking for "x", "2", and "b" in that order, with any number of characters in between. Finally, use the tilde (~) as an escape character to indicate that the next character is a *literal* like this:

```
=SEARCH("~*","apple*") // returns 6
=SEARCH("~?","apple?") // returns 6
=SEARCH("~~","apple~") // returns 6
```

The above formulas use SEARCH to find a *literal* asterisk (*), question mark (?), and tilde (~) in that order.

### More advanced formulas

The SEARCH function shows up in many more advanced formulas that work with text. SEARCH is often used instead of FIND when wildcards are needed, or when the goal is to perform a case-insensitive search. Here are a few examples:

- Cell contains one of many things - tests a cell for more than one text string simultaneously, using wildcards for flexible matching.
- Sum if cells contain either x or y - sum numbers when associated cells contain one value or another value, utilizing wildcard searches.
- Filter if text contains - extract values from a set of data with "contains-type" logic, leveraging wildcards for comprehensive searching.
- Categorize text with keywords - return an appropriate category based on if a cell contains one of several text values, using wildcards to match patterns.
- Filter based on partial match - extract records from an Excel Table based on a partial match, making use of wildcards for pattern recognition.

### Notes

- The SEARCH function returns the location of the first
*find_text*in*within_text*as a number. - SEARCH returns #VALUE if
*find_text*is not found. *Start_num*is optional and defaults to 1.- SEARCH returns 1 when
*find_text*is empty. This can cause a false positive when*find_text*is an empty cell. - SEARCH
*is*not case-sensitive but*does*support wildcards. - Use the FIND function for a case-sensitive search.
- SEARCH allows the wildcard characters question mark (?) and asterisk (*), in
*find_text*.- ? matches any single character and
- * matches any sequence of characters.
- To find a literal ? or *, use a tilde (~) before the character, i.e. ~* and ~?.