Excel Wildcard Characters

This tutorial will demonstrate how to use wildcard characters in Excel and Google Sheets.

Excel Wildcard Symbols

A wildcard is a special character that can be substituted for characters when filtering or looking up data. They are useful when working with data sets with similar, but not identical data. There are different types of wildcard characters, each of them with different uses. Here is a summary of the different types of Excel wildcards.

Excel Wildcard 1 – Question Mark (?)

The question mark symbol represents a single character.

To illustrate this, filtering for “J?ck” in the data set above provides 3 results for “Jack”, “Jeck” and “Jick”. The characters for “a”, “e” and “i” was replaced with the “?” wildcard character.

Similarly, using two question marks gave us the result of “Jorck”, where the “o” and “r” characters were replaced with “??”.

Excel Wildcard 2 – Asterisk (*)

The asterisk symbol represents any number of characters prior or after the asterisk.

To illustrate, filtering the data set above for “J*ck” provides all results where the name started with “J” and ended with “ck”. Filtering for “*eck” provided us with all results that ends with “eck”.

Filtering for “G*” provided us with all results that starts with G.

Excel Wildcard 3 – Tilde (~)

The tilde symbol is special character used in Excel to indicate that the next character is a “literal”. A “literal” in Excel is a value written exactly as it’s meant to be interpreted.

To illustrate, filtering the data set above for “J?ck~*” used a combination of the ? symbol and the ~ symbol.

“J?ck” provided all results that started with “J” and ended with “ck” with a single character in between.

The “~*” provided all results that ends with a literal “*”.

This combination allowed us to filter for only one result which was “Jack*”.

Excel Wildcard Examples

Filtered Term	Behaviour	Matches
G*	Anything that starts with G	“George”, “Green”, “Garfield123”
J???k	Anything that starts with J and ends with k and has three letters between them.	“Jiiick”, “Jaohck”, J123k”
*orc*	Anything that contains the letters “orc” in them.	“Jorck”, “That’s a big orc”, “Orca”
*~?	Anything that ends with a question mark	“Are you okay?”, “What?”, “J123?”
?*	Anything that is one character or greater	“A”, “AA”, “AAAB”
???-*	All results with three characters, a hyphen and ending with any other characters.	“123-Street”, “ABC-123”, “P12-893241231”

Excel Wildcards and VLOOKUP

 

In the example above, we have used the filtered term “*Marts” in the first field of the VLOOKUP function. This provides all results that end with “Marts”. In our example above, this is “ABC Marts”.

It’s important to be careful when using wildcard symbols as you may end up with multiple results. For example, if you were to use the Filtered Term “*M??ts”, it will provide all results that start with “M” and end with “ts” with two characters in between. In this case, there will be two results being “ABC Marts” and “Fishermans Mints”. VLOOKUP will then choose the first result that meets the criteria which in this case would be “ABC Marts” as it is higher on the table than “Fishermans Mints”.

Excel Wildcards and COUNTIF

In the example above, we have used the filtered term “*M??ts” in the criteria field of the COUNTIF function. This provides all results with words that start with “M”, end with “ts” and have two characters between. In our example above, there is “ABC Marts” and “Fishermans Mints”.

If you wanted to get a more specific result, you can use the filtered term “*M?nts”, which will then provide all results with words that start with “M” and end with “nts”. In our example, this is only “Fishermans Mints”.

Excel Wildcards with Find and Replace

You can also use wildcard symbols with Find and Replace. In the data set above, we can see different truck drivers who have entered in their work vehicle into a survey. Instead of having to replace each of the misspellings of “Truck” individually, we can replace all of them with wildcard symbols.

 

To navigate to the Find and Replace tool, click on the “Home” tab,  “Find & Select”, and then “Replace”. Alternatively, you can use the keyboard shortcut CTRL + H.

We have used the asterisk wildcard in this scenario to do the following, step by step:

1. T* indicates that anything that starts with “T” should be included
2. T*ck indicates that anything that starts with T and ends with “ck” should be included
3. T*ck* indicates anything that starts with “T”, has “ck” inside of it, and anything after “ck” should be included.

In the example above, this changed all the misspellings of “Truck”.