Written by co-founder Kasper Langmann, Microsoft Office Specialist.
In this tutorial, you’ll learn about the awesome wildcard characters in Excel.
But, what exactly are “wildcards”?
Wildcards are extremely helpful when you need to find results that are less than exact.
As you’ll see a bit later in this tutorial, the wildcards are particularly helpful in overcoming a common obstacle when using lookup functions.
Table of contents
1: Overview of the 3 wildcards (+free sample file!)
2: Most popular: The asterisk (*)
3: Using the question mark (?) as wildcard
4: Modifying wildcards with tilde (~)
5: Using wildcards and functions
The 3 different wildcards
There are three different wildcards available for use in Excel and as you can imagine, they all have a different application.
There is the asterisk (*), the question mark (?), and the tilde (~).
Each in their own slightly different manner, these wildcard characters are able to take on any value.
Each in their own slightly different manner, these wildcard characters are able to take on any value.
Kasper Langmann, Co-founder of Spreadsheeto
Follow along by downloading our sample file right below!
Download Your FREE Sample File
The asterisk as a wildcard (*)
The first wildcard we want to examine is the asterisk symbol. This is the most general wildcard of the group.
The asterisk wildcard can take on the value of any number of characters.
For instance, if we substitute the asterisk like ‘Sh*’, it could represent strings like ‘Sheet’, ‘Show’, ‘She’, ‘Shake’, ‘Shoes’, ‘Shirts’, and so on.
It can be placed at the beginning of a string like “*-01”. This would recognize all values that end with ‘-01’ regardless of the number of characters preceding it.
The usefulness of the asterisk as a wildcard should start to become a bit more obvious to you by now, right 🙂 ?
Now that we have an explanation of how the asterisk wildcard works, let’s take a look at a practical example of how we might use it with a filter on a column of data.
Kasper Langmann, Co-founder of Spreadsheeto
Let’s see exactly how you can use the asterisk wildcard
Consider: A column of organization names, some of which contain the ‘LLC’ abbreviation.
We can now filter on all those organizations in the list that are an LLC using the asterisk wildcard.
And then we get the following result…
Note that once we type ‘*LLC’ into the text box of the filter, all the entries without ‘LLC’ in it are eliminated.
Once we click ‘OK’ our list is now filtered on all entries that have ‘LLC’ at the end of the string.
Kasper Langmann, Co-founder of Spreadsheeto
Let’s see what happens when we change our filter to ‘*C’.
Notice that the filter now returns ‘WASTE MANAGEMENT OF MO INC’ in addition to all the previous organizations returned with the ‘*LLC’ search.
By eliminating the ‘LL’ from our previous filter we have further generalized and now all values ending in ‘C’ are included in the filter.
The wildcard also works in this case regardless of letter case.
We can also expand our filter by applying the asterisk to the beginning of our search string if the need calls for it.
Notice here that we were able to capture more entries from the list by expanding our wildcard search to include any number of characters both prior to and after the term ‘city’.
By using ‘city*’ we would have never captured all entries with ‘city’ in its string.
The question mark as a wildcard
The question mark as a wildcard takes on the value of any single character.
The question mark can be used to be more specific in what we are searching for – while still not being totally exact.
We can take a look at an example of how this works while building on what we already know about the asterisk as a wildcard.
Kasper Langmann, Co-founder of Spreadsheeto
Example: Let’s take a look at the question mark in action
In this next example, we will use the question mark as a wildcard for the following string: ‘c?ty’.
This will filter all entries with a substring that contains any single character between ‘c’ and ‘ty’.
However, the only way for us to get any results within a larger string is by placing the asterisk as a wildcard at the beginning and end of ‘c?ty’.
Notice that the filter shows entries that contain ‘CITY’ in the string, just as we would expect.
As a contrast, let’s replace the question mark wildcard with an asterisk.
Notice, in this case, the filter contains the same values but now there is an additional value for ‘JACKSON COUNTY MISSOURI’ since we expanded our filter by using the asterisk.
Using the tilde
What about those situations where you might want to use the wildcard characters themselves as literal characters as a part of our search?
If Excel recognizes the asterisk and the question mark as wildcards by default, how do we keep Excel from doing so in those cases where these characters are actually a part of our string?
Kasper Langmann, Co-founder of Spreadsheeto
We use the tilde, or ‘~’.
Anytime we want the asterisk or question mark to not be a wildcard, we simply place a tilde just before it.
Consider the following example where we have a table of values.
Let’s see what happens when we use the asterisk as a wildcard to filter all values that have any number of characters following an asterisk character in its string.
Based on what we have already discussed, we shouldn’t be surprised that this is our result.
But what we really want to do is isolate the value like ‘How?*’.
In order to do this, we have to place a ‘tilde’ prior to our second asterisk in the filter.
The first asterisk in our search term is the wildcard while the second asterisk is an actual character since the tilde precedes it.
Therefore, our search finds only values that end with an asterisk (in this case ‘How?*’).
Note: This same concept holds for the question mark wildcard.
Using wildcards with Excel functions
In addition to using wildcards for filtering and finding data in Excel, there are several functions in which we can also leverage the power of wildcards.
Some of these functions include ‘COUNTIF’ and ‘COUNTIFS’, ‘SEARCH’, ‘SUMIF’ AND ‘SUMIFS’, as well as ‘HLOOKUP’ and ‘VLOOKUP’.
This is an incomplete list, but the common thread is that these functions require some sort of reference or lookup criteria in which a wildcard can be useful in contrast to an exact value.
We will take a look at using the ‘*’ wildcard in a scenario involving the ‘VLOOKUP’ function.
Kasper Langmann, Co-founder of Spreadsheeto
We will use the same data we have been looking at for our filter exercises. Let’s say we want to pull in the address for our organizations we have listed in the ‘Entity Name’ column.
We will match our ‘Entity Name’ entries for each row to the ‘Taxpayer Name’ column in the lookup table.
However, there seems to be a minor issue that is going to cause us problems.
The values in the ‘Taxpayer Name’ column of our lookup table has and ID number associated that prohibits us from matching our fields exactly.
While there are different methods for approaching this challenge, we are going to leverage what we have just learned about using wildcards to make this ‘VLOOKUP’ work.
Kasper Langmann, Co-founder of Spreadsheeto
First, let’s see what happens when we try to match values in the ‘Entity Name’ column of our first table to the values in the ‘Taxpayer Name’ column of our lookup table.
Because our values do not match exactly, the ‘VLOOKUP’ returns the ‘#N/A’ error.
It does not recognize our lookup value in the lookup table.
But we can add the ‘*’ wildcard to the end of our lookup value to remedy this issue.
This only works because the ‘Taxpayer Name’ values in the lookup table are derivatives of the lookup values in our original table.
Notice that we concatenate (or join) the ‘*’ wildcard to the end of our lookup value by typing the cell reference of our lookup value followed by the ampersand (‘&’) and then the asterisk in double quotes(“*”).
The wildcard symbol needs to be added to the lookup value as a literal string and that is why the double quotes are necessary.
So you’ve just learned how to use wildcards in Excel, whether it’s the asterisk, question mark, or tilde. More importantly, you also learned how to integrate these wildcards into your every day functions. Good job!
Kasper Langmann, Co-founder of Spreadsheeto
