Wildcard in Excel

Have you ever wondered how Excel experts quickly fetch the result from the database in no time? They potentially use wildcards to perform "fuzzy" matching on data in your Excel formulas. Not only this, but wildcard characters are also used preferably in many places where you need to find the result.

In this tutorial, we will cover the types of wildcards available in Excel, including their examples and step-by-step explanation.

What is wildcard in Excel?

"Wildcards are special Excel characters that are used to take the place of the characters in them. Microsoft Excel provides three wildcard characters to use in its worksheet: an asterisk, a question mark, and a tilde. Excel uses an asterisk to return multiple numbers of characters, while a question mark denotes only a single character. In contrast, the tilde is the identification of the wild card character."

For example, if you want to look for the sales achieved by the Banglore team. But in the database, it is mentioned XYZ Banglore Team. The common word in both the words is "Banglore Team," so we can match the lookup_value with the database using the Excel wildcards, where it will match only the partial values ignoring the rest details falling in front or back of the data.

We have used the VLOOKUP function and have combined with the wildcard character * to match the value "Banglore Team" in the database and have returned its sales values.

Types of Wildcards

There are three types of wildcard characters in Excel.

Type #1 - Asterisk (*)

This wildcard character is used to match zero or the given number of characters. For instance, "Su*" could match and return an output for "Sum, Summer, Suitable, Superb," etc.

Type #2 - Question Mark (?)

This wildcard character matches any specified single character. For instance, "Fa?e" could match for "Face" & "Fate," etc.,

Type #3 - Tilde (~)

Tilde Character matches the wildcards in the word. For example, if you are given the word "My*" to look for this Value in any cell, we need to specify the sentence as "My~*." So here, the character tilde (~) specifies the word "Hello" and it will not return the wildcard character *.

Advantages of Wildcard Characters

The benefits of using wildcard characters in Excel are as follows:

1. Wildcards allows more flexibility when the Excel value is nor defined or is partially available.
2. These are very useful to search or look for a text with a partial match.
3. You can merge them with any Excel function or can create your own customised formulas.

Wildcard Characters Examples

Let's evaluate some practical applications of Wildcard Characters in our day-to-day Excel life.

Example #1 - Usage of Excel Wildcard Character Asterisk (*)

We are given the following data.

As you can see, there are repetitive instances of word Rahul. Now, what if you want to find the total count of the word Rahul.

1. We will use the Countif formula to calculate the occurrences of the common word "Rahul'.
2. Start the formula with = followed by Countif. It will ask for two parameters, in the range enter the data ranage, whereas in the criteria section add the word Rahul.
   Formula Used =COUNTIF(A2:A10, "Rahul")
   
3. It will return an output 0 because we have only mentioned Rahul in the criteria, and there is no occurrence of the single word Rahul. To get the desired output, we must add the wildcard character.
   
4. Again enter the Countif function, and we will do some tweaking in the criteria range. Instead of "Rahul", we will put "Rahul*".
   Formula Used =COUNTIF(A2:A10, "Rahul*")
   
5. Press enter once you are done. As a result, it will count all the occurrences of the word Rahul in the specified data range. You will have the following output.
   

Example #2: Partial Lookup Using Wildcard Characters & VLOOKUP

Wildcard characters and the VLOOKUP function perfectly assist Excel users in finding and matching an exact lookup value and fetching the resulting data. For instance, in the below table, if we want to look for the value "Mouse", but in the lookup table, it is "Computer Mouse".

If we apply the regular VLOOPUP function, it won't match the looking data from the main table (check the below image) because it requires an exact lookup_value to be matched to return the output. In case you write a VLOOKUP function with an approximate match, it will return a mismatched result.

So here, we can use the wildcard characters to match it up with the table. The following are the steps to apply wildcard characters with the Excel VLOOKUP function:

1. Select a cell where you want to enter the VLOOKUP formula. We have selected the cell C2.
   
2. Type the VLOOKUP formula, and it will ask you to specify the parameters. In the lookup_up value argument, we will merge it with the Excel wildcard character asterisk (*) - "*"&C2&"*". As you can see, the character '*' is flanked on both sides of the cell reference C2.
3. Specify the other parameters including the table_array, and col_index_number.

1. Press enter button and Excel will return the following output. As you can see, it matches only the required value respective if the cell contains anything in front or back of the data.
   
2. Drag the formula to other cells and replicate the formula. It will return the values for the entire table.

Example #3: Find and Replace Partial Matches

Many times we fetch the Excel data from different database software. While transporting the data, Excel converts some unrecognized values into some symbols. For example, in the below data, we have JavaTpoint, but in between, there are various unknown symbols. Now the question is how to remove them at once. You can create a complex formula or can even opt for a basic one formula.

In such cases, Excel Wildcard characters are pretty versatile. We just need to use it with Find and Replace function, and your job will be done in one click. The following are the steps to clean the given data and make it consistent for further use. We can use Find and Replace with Excel wildcard characters.:

1. Select the data where you want to find and replace text. Here we have selected the cells A2:A7.
   
2. From the Excel ribbon tab, click on Home -> Find & Select -> Replace.
   
3. The following Find and Replace window will appear. The shortcut key to open the window is CTRL + H.
   
4. In the Find_What box, enter the value with the wildcard character '?' i.e., Java?point
5. In the Replace_with box, type the value that you want i.e., JavaTpoint.
6. Once done, click on the Replace All button present at the bottom.
   
7. Excel will throw another pop-up window mentioning how many characters it replaced click on OK.
   
8. That it's Excel will replace all the unrecognized characters with the given value.

Simple isn't it?? Just one click and all the special characters are replaced by a 'T' character, and we will have the required data of "Javatpoint", which can be used further in other operations.

You can use the Wildcard Characters in n number of places, merge with different excels, or create customized formulas to find partial data outputs. Trust me, and it converts complex formulas into one-liners. Go for it and give it a try now!