Exceljet

Quick, clean, and to the point

Excel SEARCH Function

Excel SEARCH function
Summary 

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.

Purpose 
Get the location of text in a string
Return value 
A number representing the location of find_text.
Syntax 
=SEARCH (find_text, within_text, [start_num])
Arguments 
  • find_text - The text to find.
  • within_text - The text to search within.
  • start_num - [optional] Starting position in the text to search. Optional, defaults to 1.
Version 
Usage notes 

The SEARCH function returns the position (as a number) of one text string inside another. If there is more than one occurence 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 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.

Examples

To return the position of the first "a" in the word "Apple":

=SEARCH("a","Apple") // returns 1

Note hardcoded text values must be enclosed in double quotes ("").

To find the first match of "the" in any combination of upper or lowercase:

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

Same search, starting at character 4:

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

Wildcard usage:

=SEARCH("?at","The cat in the hat") // returns 5

Error if not found:

=SEARCH("dog","The cat in the hat") // returns #VALUE!

SEARCH can be used together with the ISNUMBER function to test for specific text in a cell:

=ISNUMBER(SEARCH(substring,text)) // returns TRUE or FALSE

See below for examples of search in more advanced formulas.

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 ~?.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.