Using wildcards and searching specific text in Excel

When you want to search for some specific text rather than an exact match, you might be used to using wildcard characters in other programs. For example, the asterisk * is the wildcard in Microsoft Access and can be used alongside the Like operator. On a list of US states if you added criteria of Like “New*” it would bring back all that begin with “New”:

 

New Hampshire
New Jersey
New Mexico
New York

 

There isn’t a LIKE function in Excel but you can simulate it using the FIND or SEARCH functions. There isn’t much difference between the two, they both return the position in the string of the text you’re searching for, but FIND is case-sensitive and SEARCH isn’t. They both scan from left to right and return the character position of the searched text.

 

To demonstrate the fact that these are case-sensitive the FIND formula in D27 is:
=FIND(“city”,C27)

A capital C in the FIND formula above would return 11, because the C of City is the 11th character in “Jefferson City”. With the SEARCH function case doesn’t matter, and the formula in E27 is:
=SEARCH(“city”,C27)

 

 

Continuing with the SEARCH function as the example, you will see that a Value error is returned for any entries which don’t include “City” within their text.

 

 

If you want to tidy this up, one method would be to convert the data to a Boolean (True or False) using the ISNUMBER function. ISNUMBER simply returns true if the value is numeric and false if it isn’t. As both FIND and SEARCH return the character position when it finds a match, we can go with any that ISNUMBER find True must contain “City”. E.g. for E27 the formula becomes:
=ISNUMBER(SEARCH(“city”,C27))

 

 

There are two wildcard values that Excel accepts, the question mark ? and the asterisk * are the two characters you can use. They have different uses, a question mark represents one unknown character whereas an asterisk represents any number of unknown characters. They can be used in a standard Find & Replace (Ctrl + H) but also within certain functions, including FIND and SEARCH.

 

For example, if you just wanted to return a True for all state capital cities that include the letter L anywhere in their name you could search for *L*.

 

So the formula in cell E3 here is:
=ISNUMBER(SEARCH(“*L*”,C3))

 

Leave a Reply

Your email address will not be published. Required fields are marked *