Do you use wildcard characters in excel?
They are life savers.
Wildcard characters are all about searching a text with a partial match.
Let’s say you have a list of names & you want to search for the names who are starting with “T” and ending with “I”.
You can use a question mark (?) to search for the names with your criteria.
In this post, you will learn how to use wildcard characters in excel. And, you will also learn some real life examples to use wildcard characters.
Table of Content
- Using with SUMIF
- With VLOOKUP
- In Find & Replace
- For Conditional Formatting
- In Filter Values
- With Numbers
We have three wildcard characters.
- Asterisk (*) – It 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.
- Question Mark (?) – By using question mark you can find text in which ? will represent a letter. For Example: If you use P?inter it will give you words like Printer & Painter.
- Tilde (~) – By using tilde you can nullify the impact of above two wildcard characters. For Example: If you want to search for a text Pivot*, Find & Replace option will take Pivot as a text & * as a wildcard. But if you use Pivot*~ it will exactly find a text with Pivot*.
Now, let’s come to some examples to understand how you can use wildcard characters in excel.
Using SUMIF with Wildcard Characters
If look at the below data in which I have some invoice numbers with the amount.
And, I want to sum amounts where invoice numbers have “Product-A” in starting.
For doing this I will use sumif function with wildcard characters.
In above formula, I have used an asterisk (*) with criteria. Now, sumif will sum all the amounts where invoice no. have “Product-A” in the starting.
VlookUp with Wildcard Characters
Let’s say you have a list of students with full name & their marks.
Now, if you want to use VLOOKUP to get marks in another list in which you only have their first names.
Formula will be =VLOOKUP(Name&”*”,$D$2:$E$9,2,0).
In above example, I have used vlookup with an asterisk to get marks by only using the first name. It will return marks where get the first name in the cell.
Wildcards Characters in Find & Replace
Using wildcard characters with find & replace option can do wonders in your data.
Let me show you an example.
In above example, I have a word “ExcelChamps” which has different characters between it. And, I want to replace those characters with a simple space.
I can even do this by using replace option but in that case, I 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.
Wildcard Characters for Conditional Formatting
You can also use these wildcard characters with conditional formatting.
Let’s say, in above list, you want to highlight the name of the cities which are starting from letter A.
- Select the first entry from the list.
- Go to Home → Styles → Conditional Formatting.
- Click on “New Rule” option.
- Select “use a formula to determine which cells to format”.
- In formula input box, enter the formula. =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 & it will highlight all the names starting with letter A.
WildCard Characters In 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 me 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.)
Apply WildCard Characters In Excel If You Only Have Numbers
You can use these wildcards with numbers but you have to convert those numbers into text.
In above example, I have used match function & text function to perform a partial match.
Text function will convert entire range into the text & in match function, you can use lookup values with an asterisk to perform a partial match.
This is not the end of the story.
You can use wildcards characters in excel with different functions.
Using wildcard characters in Excel let you manipulate your data in a smart way.
I hope these wildcard characters will help you.
Over To You
Do you have any other idea to use wildcard characters?
Share with me in the comment box.
- R1C1 Reference Style In Excel
- Barcode Generator Template In Excel
- Add Leading Zeros In Excel Before Numbers