What is the SEARCH function in Excel?
The SEARCH function returns a number, corresponding to the position of the 1st character of the searched term within a text. It can be used for various reasons.
The function has 2 required arguments and 1 optional (start_num).
Syntax of the SEARCH formula: =SEARCH(find_text,within_text,start_num).
find_text – the text you want to find. It has to be within “”.
within_text – the text in which you want to search for find_text.
start_num – the position number you want the search to start from.
Notes
– You can use wildcard characters in the find_text argument. A question mark (?) will search for any single character and an asterisk (*) will match any sequence of characters.
– If you leave the start_num argument empty, SEARCH will start searching for find_text from the first character within within_text.
– If the find_text argument is not found, an error value will appear: VALUE!.
– The SEARCH function is not case sensitive in MS Excel: it does not make any difference between lowercase and uppercase letters.
– If searched characters appear several times within the within_text argument, the SEARCH function will not analyse find_text after the 1st result.
Examples
Use of the SEARCH function – Specific word
In this example, we want to extract all the email addresses containing the domain “tedjordan.org”. All cells resulting in positive numbers confirm the presence of the searched text.
Use of the SEARCH function – Wildcard characters
In this example, let extract all products available in specific sizes (not in unique size) using the wildcard character *; find_text is “size”. All cells containing positive numbers confirm the presence of a specific size reference.
Use of the SEARCH function – From a specific position
We want to find all references mentioning sizes in 40s. If we don’t specify the start_num, results will be incorrect because 40s numbers can be found at the beginning of some references.
find_text “4?” so sizes not starting with 4 will be excluded (as per row 5).
How to replace error values while using SEARCH in Excel?
If you want to replace ugly error values while using the SEARCH formula in Excel, combine it with IFERROR!
Example
We want to extract all references mentioning specific sizes; to change VALUE! errors, we use the IFERROR function by replacing any error values by “Unique size”.
What’s the difference between SEARCH and FIND in Excel?
SEARCH and FIND functions work the same way but SEARCH is not case sensitive while FIND is.
Now, you know how to use the SEARCH function in Microsoft Excel!
Join our Course now to discover more tips and tricks about MS Excel!
