Using Excel's wildcard character

At times, you may want to use certain string matching or search functions — like SEARCH — without knowing exactly what you're looking for. For example, you may want to search for a state that begins with the word "New" — but match against all possible outcomes, including "New Hampshire", "New York", and "New Jersey". To do this, you can use something called a "wildcard" character to tell Excel that you're looking for something, but you're not exactly sure what yet.

Think of Excel's wildcard characters like the jokers in a deck of cards. They can take on any value they want to.

Here's some more information on how wildcards work:

The basic wildcard structure

There are two key types of wildcards in Excel. Here's what they look like and how you use them.

Per the chart above, use the question mark (?) when you want to accept only a single character, and the asterisk (*) when you want to accept multiple characters.

Here are a few examples of wildcards in action with the SEARCH function:

=SEARCH("c?d", "Abracadabra")
Output: 5

The above returns the number 5, because "c?d" matches the phrase "cad", which is found five characters in to the given string.

=SEARCH("c*b", "Abracadabra")
Output: 5

The above returns the number 5, because "c*b" matches the phrase "cadab", which is found five characters in to the given string.

=SEARCH("c?b", "Abracadabra")
Output: #VALUE!

The above returns a #VALUE! error, because "c?b" does not match any phrase within the given string. There is no instance in which a "c" and a "b" are only separated by one letter.

When can I use wildcards?

You can use wildcards in any situation in which you are matching one string against another. There are many such uses, but here are some of the most common:

• When searching for a string using the SEARCH or FIND functions;
• When performing a VLOOKUP or HLOOKUP; and
• When using INDEX MATCH.

Wildcards can be extremely helpful if you want to match or search against a partial phrase rather than an entire string.

Comments