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 letter "A" in the word "Apple":

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

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

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

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

Starting at position 4:

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

Wildcard usage:

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

Notes

  • SEARCH returns the position of the first character of find_text inside within_text.
  • Start_num is optional and defaults to 1.
  • Unlike FIND, SEARCH is not case-sensitive and allows wildcards.
  • SEARCH allows the wildcard characters question mark (?) and asterisk (*), in find_text.
  • The ? matches any single character and the * 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.