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:
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.
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:
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.:
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!