Search() function

November 7, 2020
Text Function

The SEARCH function in Excel is very similar to FIND in that it also returns the location of a substring in a text string. Unlike FIND, the SEARCH function is case-insensitive and it allows using the wildcard characters.

Syntax

The syntax for the SEARCH function in Microsoft Excel is:

=SEARCH( substring, string, [start_position] )

Parameters or Arguments

substring

The substring that you want to find.

string

The string to search within.

start_position

Optional. It is the position in string where the search will start. The first position is 1.

Search Function Examples

Column B of the following spreadsheet shows examples of the Excel Search function, used to search for various characters within the text string “Original Text”.

Formulas:

Results:

Note that, in the above examples:

  • As the Search function is not case sensitive, the upper- and lower-case search_text values, “T” and “t”, return the same result (see cells B1 & B2).
  • In the example in cell B4, the [start_num] argument is set to 4. Therefore the search begins at the fourth character of the within_text string and so returns the second occurrence of the character “i”.

Note

  • If the SEARCH function does not find a match, it will return a #VALUE! error.
  • SEARCH returns the position of the first character of find_text inside within_text.
  • Unlike FIND, SEARCH allows the use of wildcards, and is not case-sensitive.
  • 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 ~?.

Common Error

#VALUE!

Occurs if either:

  • The supplied search_text is not found in the supplied within_text string;
  • The supplied [start_num] is less than zero or is greater than the length of the supplied within_text string.

Excel Function Home

Text Functions