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
Sometimes while looking for a match or searching for a value in Excel, we don’t have the exact value to use. For example, we want to search for “Delhi” and in the actual data, we have value “New Delhi”.
Here both have “Delhi” in them but don’t have an exact match, Now, in this situation, we can’t match or search.
So, we need a way which can allow us to use only available value or partial value (Delhi) to match or search for the actual value (New Delhi).
Wildcard Characters are meant for this. These characters are all about searching a text with a partial match.
In Excel, the biggest benefit of a wildcard is with lookup functions. It gives more flexibility to all those functions.
So today, in this post, I'd like to share with you how to use wildcard characters, their types and examples to use them with different functions.
So let's get started.
In Excel, we have 3 wildcard characters. Each of these has a its own significant and can help you to take up any value.
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.
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.
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*.
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 we use for matching a value, for lookups or find a text.
Ahead, we will learn to use wildcards with functions + find and replace options + conditional formatting + with numbers.
Below we have data where we have some invoice numbers with the amount. And, we need to sum amounts where invoice numbers have “Product-A” in starting.
For doing this we can use SUMIF with wildcard characters. And the formula will be:
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.
Let’s say we have a list of students with full name and their marks. Now, we want to use VLOOKUP to get marks in another list in which we only have their first names.
And for this the formula will be:
In above example, I have used vlookup with an asterisk to get marks by only using the first name.
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.