These Excel SEARCH function examples show how to look for a text string, within another text string, and return its position, if found. Video, written steps and free workbook
The Excel SEARCH function looks for a text string, within another text string, and returns its position, if found. Watch this short video to see 3 examples of how to use SEARCH. There are written steps below the video.
The SEARCH function looks for a text string, within another text string, and it can:
The SEARCH function has the following syntax:
The SEARCH function has a few traps:
Use the SEARCH function to look for text within a text string. In this example, we're looking for a single character (entered in cell B5), within a text string in cell B2.
=SEARCH(B5,B2)
If the text is found, the SEARCH function returns the number of its starting position in the text string. If it's not found, the result is a #VALUE! error.
You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR.
=IFERROR(SEARCH(B5,B2),"Not Found")
Another way to check the SEARCH results is with the ISNUMBER function. If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE. If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE.
You can also use wildcards in the find_text argument. The * (asterisk) represents any number of characters, or no characters, and the ? (question mark) represents a single character.
In this example, the * wildcard is used, so central, center and centre are all found in the street addresses.
=ISNUMBER(SEARCH($E$2,B3))
By typing two minus signs (double unary) in front of the ISNUMBER function, it returns 1/0 instead of TRUE/FALSE. Then, a SUM function in cell E2 can total the number of records where the text string was found.
In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The formula in cell C2 is:
=–ISNUMBER(SEARCH($E$1,B2))
The formula found the string that contain "bank", but one of those is in a City name, not the occupation:
There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching.
Now, with the revised formula, only the rows with "bank" in the occupation are counted.
=–ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
To see the formulas used in these examples, download the SEARCH function sample workbook. The file is zipped, and is in Excel xlsx format, with no macros.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: March 4, 2021 7:40 PM
Contextures RSS Feed