SEARCH
Information Functions
Basic Description
The Excel SEARCH function returns the position of a specified character or 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 :
Where the function arguments are:
| search_text | - | The character or text 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 of the character 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 within the Search function:
? - matches any single character
* - matches any sequence of characters
If the supplied search_text is found, the Search function returns a number, representing the position of the search_text in the within_text. If the supplied search_text is not found, the Excel #VALUE! error is returned.
Search Function Examples
The following spreadsheet shows examples of the Excel Search function used to search for various characters within the text string "Original Text".
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 position 5 as the position for the character "i".
Example 2 - Use of the Search Function to Show Cells Containing Specific Text
The example on the right uses the Excel Search function to highlight cells containing a specific text string.
The example spreadsheet lists a company's members of staff, in column A and their office skills in column B. The Search function is used in columnn C, to highlight the members of staff who have the skill 'Typing'.
If the string "Typing" is found, the Search function returns the position of the start of this string within the searched cell. If the string "Typing" is not found, the function returns the #VALUE! error.
Note that cell B3 contains the string "typing" (in lower case). However, because the Search function is not case sensitive, it still matches this with the search_text "Typing" and so returns the start position 19 (see cell C3).
You might want to tidy up the results of the Search function in the example above. This can be done using the If and Iserror functions. For example, the formula in cell C1 could be written as
This would return the value 1 if the text "Typing" was found in cell B1 and 0 otherwise.
Further information and examples of the Excel Search function can be found on the Microsoft Office website.
Search Function Error
If you get an error from the Excel Search function this is likely to be the #VALUE! error:
| #VALUE! | - |
Occurs if either:
|