Summary

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of find_text inside within_text as a number. SEARCH supports wildcards, and is not case-sensitive.

Purpose 

Get the location of substring in a string

Return value 

A number representing the location of substring

Arguments 

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

Syntax 

=SEARCH(find_text, within_text, [start_num])

Usage notes 

The SEARCH function returns the position (as a number) of one text string inside another. If there is more than one occurrence of the search string, SEARCH returns the position of the first occurrence. SEARCH is not case-sensitive but does support wildcards. Use the FIND function to perform a case-sensitive find. When SEARCH doesn't not find anything, it returns a #VALUE error. Also note that when find_text is empty, SEARCH will return 1. This can cause a false positive when find_text comes from a cell, and the cell is empty.

Basic Example

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!

Note that text values entered directly into SEARCH must be enclosed in double-quotes ("").

TRUE or FALSE result

To force a TRUE or FALSE result, nest SEARCH inside the ISNUMBER function. ISNUMBER returns TRUE for numbers and FALSE for anything else. 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, and ISNUMBER returns FALSE. 

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 "the", you can omit start_num, which defaults to 1:

=SEARCH("the","The cat in the hat") // returns 1

To start searching at character 4, enter 4 for start_num:

=SEARCH("the","The cat in the hat",4) // returns 12

Wildcards

Although SEARCH is not case-sensitive, it does support wildcards (*?~). The formula below looks for a substring 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

If cell contains

To return a custom result with the SEARCH function, use 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.

Notes

  • SEARCH returns the position of the first find_text in within_text.
  • Start_num is optional and defaults to 1.
  • 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 ~?.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.