SEARCH Function

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("x",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, 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 - sums numbers when associated cells contain one value or another, utilizing wildcard searches.
• Filter if text contains - extract values from a data set 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, using 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
  • * matches any sequence of characters.
  • To find a literal ? or * or ~, use a tilde (~) before the character, i.e. ~*, ~?,~~.