Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
10000+ Copies Already Downloaded
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
We have three wildcard characters.
Now, let’s come to some examples to understand how you can use wildcard characters in excel.
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.
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.
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.
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.
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.)
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.
Do you have any other idea to use wildcard characters?
Share with me in the comment box.