Home > Formulas > Text > SEARCH

Excel SEARCH Function

These Excel SEARCH function examples show how to look for a text string, within another text string, and return its position, if found. Video, written steps and free workbook

Author: Debra Dalgleish

Video: SEARCH Function

The Microsoft Excel SEARCH function looks for a text string, within another text string, and returns its position, if found. Watch this short video to see 3 examples of how to use SEARCH.

There are written steps below the video.

Uses for SEARCH

The SEARCH function looks for a text string, within another text string, and it can:

• Find a text string within another text string – not case sensitive
• NOTE: For a case-sensitive search, use the FIND function
• Use wildcards in the search
• Specify a start number within search text

SEARCH Syntax

The SEARCH function has the following syntax:

• SEARCH(find_text,within_text,start_num)
  • find_text is the text that you're looking for.
  • within_text is the string that you're searching in.
  • if start_num is omitted, the search starts with the first character

SEARCH Traps

The SEARCH function has a few traps:

• SEARCH function returna the position of the first matching string, regardless of case.
• If you need a case sensitive search, use the Excel FIND function

Example 1: Find Text in a String

Use the SEARCH function to look for the first occurence of text within a text string. In this example, we're looking for a single character (entered in cell B5), within a text string in cell B2.

=SEARCH(B5,B2)

If the text is found, the SEARCH function returns the number of its starting position in the text string. If it's not found, the result is a #VALUE! error.

You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR.

=IFERROR(SEARCH(B5,B2),"Not Found")

Ex 2: SEARCH with Wildcards

Another way to check the SEARCH results is with the ISNUMBER function.

• If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE.
• If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE.

You can also use wildcard characters in the find_text argument.

• The * (asterisk) represents any number of characters, or no characters,
• The ? (question mark) represents a single character.

In this example, the * wildcard is used, in cell E2, within the sequence of characters to search for.

The following formula is in cell C3, to search for the street addresses that contain that search text:

• =ISNUMBER(SEARCH($E$2,B3))

In column C, rows 3, 4 and 7 show a result of TRUE, because each of those street addresses contain:

• "cent"
• followed by any other string of characters (or no characters)
• followed by the letter "r"

NOTE: If you want to search for an actual question mark or asterisk, type a ~ (tilde character) in front of the character. For example, to search for mini*mart, type this criteria: mini~*mart

Ex 3: Specify Start Number for SEARCH

By typing two minus signs (double unary) in front of the ISNUMBER function, the formula returns a result of 1 (one) or 0 (zero) instead of TRUE/FALSE.

With those numeric results, a SUM function in cell E2 can total the number of records where the text string was found.

In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1.

The below formula is in cell C2, with two minus signs before the ISNUMBER function:

• =–ISNUMBER(SEARCH($E$1,B2))

In the screenshot below, the formula found the string that contain "bank", but one of those is in a City name, not the occupation:

There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching.

Now, with the revised formula, only the rows with "bank" in the occupation are counted.

=–ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))

Ex 4: Reverse Names - Long Formula

In this example, there is a list of names on an Excel spreadsheet. Currently, the names are in this format - Last Name, First Name.

To reverse the names (First Name Last Name), you can use the following Excel formula. Along with the SEARCH function, it uses the RIGHT function, LEN function, and LEFT function.

• =RIGHT(A2,LEN(A2) - (SEARCH(",",A2,1) + 1)) & " "
  & LEFT(A2,SEARCH(",",A2,1) -1)

Video: Reverse Names - Long Formula

This video shows how to build the formula, and there are written steps, and the full video transcript, on the Split Names with a Formula page.

• Note: You can also reverse the names, with the MID function, in a shorter formula. You can see that formula on the Split Names with a Formula page. I've included this long formula here, in case you saw it in someone's Excel file, and you want to understand how this complex formula works.

Get the Sample Files

• SEARCH Function: To see the formulas used in these examples, get the SEARCH function sample workbook. The file is zipped, and is in Excel xlsx format, with no macros.

Excel Topics

Data Val | Pivot | Formulas | Macros | Format | Charts | Filters

More Functions Tutorials

FIND Function

Excel Find and Replace

VLOOKUP Function

Named Excel Tables