What is the SEARCH function in Excel?
The SEARCH function returns a number, corresponding to the position of the first character of the searched term within a text. It can be used for various reasons.
This spreadsheet search function has two required arguments and one optional (start_num).
In Excel, the SEARCH formula is =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.
Tips to search data in Excel
– You can use wildcard characters in Excel, 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 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.
The SEARCH formula is =SEARCH(“tedjordan.org”,A2)
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.
The SEARCH formula we used in the example is =SEARCH(“size*”,A2)
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).
We combined wildcard characters in Excel with the SEARCH function. The formula we used in this example is SEARCH formula is =SEARCH(“4?”,A2,8)
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!
SEARCH function with IFERROR in Excel – 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”.
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 search data in Excel using the SEARCH function!
Join our Online Excel Course with Certification now to learn more Excel tips and tricks and prove your Excel proficiency.
