Sometimes working with data is not that easy, sometimes we face complex problems.
Let say, you want to search for the text “Delhi” from the data. But, in that data, you have word “New Delhi” not of “Delhi”.
Now, in this situation, you can’t match or search.Here, you need a way which allows you to use only available value or partial value (Delhi) to match or search for the actual value (New Delhi).Excel Wildcard Characters are meant for this.These wildcard characters are all about searching/looking up for a text with a partial match.In Excel, the biggest benefit of these characters is with lookup and text functions. It gives you more flexibility when the value is unknown or partially available.So today, in this post, I’d like to tell you what are wildcard characters, how to use them, their types and examples to use them with different functions.So let’s get started.
What are Excel Wildcard Characters
Excel Wildcard Characters are special kind of a characters that represent one or more other characters. It’s used in regular expressions, by replacing them with unknown characters. In simple words, when you are not sure about an exact character to use, you can use a wildcard character in that place.
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.
An asterisk is one of the most popular wildcard characters.
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.
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.
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 c?amps 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.
Wildcard Characters with Excel Functions
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 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.
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:
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.
In above example, you have a word “ExcelChamps” which has different characters between it. And, you want to replace those characters with a simple space.
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.
Download this sample file from here to learn more.
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.
Have you ever used these wildcard characters before?
Share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.
- Excel 3D Reference
- Hide Formula in Excel
- R1C1 Reference Style in Excel
- Excel Wildcard Characters – What are they and How to use them.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.