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.
1 thought on “Search() function”
Comments are closed.