The Excel SEARCH Function

Related Function:

Function Description

The Excel SEARCH function returns the position of a specified character or sub-string within a supplied text string.

The function is not case-sensitive. If you want to perform a case-sensitive search, use the Excel Find function instead.

The syntax of the Search function is:

SEARCH( search_text, within_text, [start_num] )

Where the function arguments are:

search_text	-	The character or sub-string that you wish to search for.
within_text	-	The text string that is to be searched.
[start_num]	-	An optional argument that specifies the position in the within_text string, from which the search should begin. If omitted, this takes on the default value of 1 (i.e. begin the search at the start of the within_text string)

Wildcards

You can also use the following wildcards in the search_text argument:

? - matches any single character
* - matches any sequence of characters

e.g. the condition "A*e" will match any substring beginning with "A" and ending in "e".

If the supplied search_text is found, the Search function returns a number representing its position in the within_text. If the supplied search_text is not found, the function returns the Excel #VALUE! error.

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:

	A	B
1	Original Text	=SEARCH( "T", A1 )
2	Original Text	=SEARCH( "t", A2 )
3	Original Text	=SEARCH( "i", A3 )
4	Original Text	=SEARCH( "i", A4, 4 )

Results:

	A	B
1	Original Text	10
2	Original Text	10
3	Original Text	3
4	Original Text	5

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".

See the Microsoft Office website for further details and examples of the Excel Search function.

Search Function Error

If you get an error from the Excel Search function this is likely to be the #VALUE! error:

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.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions