Table of Contents
What Are The Types of Excel Wildcard Characters?
In Excel, you have 3 different wildcard characters Asterisk, Question Mark, and Tilde. And each of these has their own significance and usage.
Ahead we’ll discuss each of these characters in detail so that you would be able to use them in different situations.
1. Asterisk(*)
An asterisk is one of the most popular wildcard characters.
You can find any number of characters (in sequence) from a text. For example, if you use Pi* it will give you words like Pivot, Picture, Picnic etc.
If you notice, all these words have Pi in starting and then different characters after that. Let’s understand its working with an example.
Below you have a data where you have names of cities and their classification in a single cell.
Now from here, you need to count the number of cities in each classification. and for this, you can use an asterisk with COUNTIF.
=COUNTIF(City,Classification&"*")
In this formula, you have combined classification text from the cell with an asterisk. So, the formula only matches the classification text in the cell and the asterisk works as rest of the characters.
And in the end, you get the count of cities according to classification.
2. Question Mark
With a question mark, you can replace one single character from a text. In simple words, it helps you get more specific, still using a partial match.
For example, if you use champs it will return champs and chimps from the data.
3. Tilde (~)
The real use of a tilde is to nullify the effect of a wildcard character. For example, let’s say you want to find the exact phrase pivot*.
If you use pivot* as a string, it would give you any word that has champs at the beginning (such as pivot table, pivot chart).
To specifically the string pivot*, you need to use ~. So you string would be pivot~*. Here, ~ ensures that Excel reads the following character as is, and not as a wildcard.
We can easily use all the three wildcard characters with all the top most functions.
Functions like VLOOKUP, HLOOKUP, SUMIF, SUMIFS, COUNTIF, COUNTIFS, SEARCH, FIND, and INDEX MATCH.
You can extend this list with all the functions which you use for matching a value, for lookups or find a text.
Ahead, I’ll share with you some of examples with functions + find and replace options + conditional formatting + with numbers.
1. With SUMIF
Below you have data where you have invoice numbers and their amount. And, we need to sum amounts where invoice numbers have “Product-A” in starting.
For doing t
his we can use SUMIF with wildcard characters. And the formula will be:
=SUMIF(F2:F11,”Product-A*”,G2:G11)
In the above example, we have used an asterisk with the criteria at the end. As we have learned an asterisk can find a partial text with any number of sequence characters.
And, here we have used it with “Product-A”. So, it will match the values where “Product-A” is at the beginning of the value.
2. With VOOKUP
Let’s say you have a list of students with full name and their marks.
Now, you want to use VLOOKUP to get marks in another list in which you only have their first names.
And for this the formula will be:
=VLOOKUP(first_name&”*”,marks_table,2,0)
In above example, you have used vlookup with an asterisk to get marks by only using the first name.
3. For Find and Replace
Using wildcard characters with find and replace option can do wonders in your data.
You can even do this by using replace option but in that case, you have to replace each character one by one.
But, you can use a question mark (?) to replace all the other characters with space. Find “Excel?Champs” & replace it with “Excel Champs” to remove all unwanted characters.
4. In Conditional Formatting
You can also use these wildcard characters with conditional formatting.
Let’s say, from a list, you want to highlight the name of the cities which are starting from letter A.
- First of all, select the first entry from the list and then go to Home → Styles → Conditional Formatting.
- Now, click on “New Rule” option, select use a formula to determine which cells to format”.
- In formula input box, enter =IF(COUNTIF(E2,”A*”),TRUE,FALSE).
- Set the desired formatting, you want to use to highlight.
- Now, I have used conditional formatting in the first cell of the list & I have to apply it to the entire list.
- Select the first cell on which you have applied your formatting rule.
- Go to Home → Clipboard → format painter.
- Select entire list and it will highlight all the names starting with letter A.
5. Filter values
Wildcard characters work like magic in filters.
Let’s say you have a list of cities and you want to filter city names starting from A, B, C or any other letter.You can use A* or B* or C* for that.
This will give you the exact name of cities which are starting from that letter.
And, if you want to search any character which is ending with a specific letter you can use it before that letter (*A, *B,*C, etc).
6. With Numbers
You can use these wildcards with numbers but you have to convert those numbers into text.
In above example, you have used match function and text function to perform a partial match.
Text function returns an array by converting entire range into the text and after that match function, lookup for a value which is starting from 98.
Conclusion
With wildcard characters, you can enhance the power of functions when your data is not in a proper format.
These characters help you search or match a value using a partial match which gives you a power deal with irregular text values.
And, the best part is you have three different characters to use.
