In this blog, we would learn what are the wildcard characters and how can you use the wildcard characters in Excel to filter your data or do a partial Vlookup and many more exciting things.
Excel only uses three wildcard characters which are-
- Asterisk (*)
- Tilde (~)
- Question Mark (?)
Wildcard Characters in Excel – Meaning and Usage
Wildcard characters are those special characters that are used to do non-exact matches of text in Excel.
For example- If you have a list of texts in a column in Excel and you want to filter out all those texts that start with the letter “P” or the word “Excel”, in this case, the wildcard characters can prove to be life-savers for you.
It is pertinent to note that wild card characters in excel only works for text characters and not for the numbers.
Working With Wildcard Characters
As mentioned in the introduction, there are only three such special characters in Excel (*, ~, and ?). Let us understand how each of these would work.
- Asterisk (*) – The use of Asterisk * is Excel is the highest amongst the three wildcard characters. It is used when you want to find any number of characters before, after, or on either side of the text. Like Diff* would mean any number of characters after the word “diff”. For example – differ, difference, differentiation, and likes. Similarly, *ps would mean any number of characters before the word “ps”. For example – lollipops, raindrops, teardrops, etc. When you write a word in between the asterisk * character, then it would mean any number of characters before and after that word. For example – *cell* would mean unicellular, subcellular, and likes.
- Question Mark (?) – The question mark represents a character i.e. ? as one character, ?? as two characters and so on. For example – ?ite may represent Kite, Bite, Site, etc (One ? = One Character), but not white (two characters before ite). However, ??ite would represent White (Two ?? = Two characters).
- Tilde (~) – The Use of Tilde ~ in Excel is very limited. However, it is a very important wildcard character when searching for a text that also contains some wildcard characters. For example – If you have an exact word (with wildcard character) like Car*. When you simply use to find this word by using the word Car*, it would return everything that begins with the word Car (as learned above) like carry, carrying, etc. In that case, the tilde character can be helpful as it would ignore the wildcard character and consider it as a normal text. Use Tilde (~) between the word and the wildcard character like this – Car~*. This would search for an exact word Car*, by ignoring the wildcard character *.
Let us now go through its use in your daily excel tasks.
Filter Using Wildcard Character
The wildcard characters are most for filtering the data when you want to apply a filter for partial matches.
Look at the below image containing the list of email addresses:
Suppose you want to filter the email addresses based on conditions like – email addresses belonging to excelunlocked, or that starts with the letter ‘a’, and so on.
The asterisk (*) character would be a very handful tool to achieve it.
To find email addresses that belong to excelunlocked, simply suffix and prefix the word excelunlocked with an asterisk character in the search box while filtering.
Similarly, to find all the email addresses that begin with the letter ‘a’, suffix the character ‘a’ with asterisk * (a*) in the filter search box.
Using Special Characters with VLOOKUP
The VLOOKUP function in Excel finds for an exact match of the text in a table or dataset and returns the result based on the column index.
However, combining the VLOOKUP function with the wildcard characters, you can even perform a partial match of text. The approximate match attribute of VLOOOKUP may not always give a correct result for partial matches.
We have a detailed blog on Using VLOOKUP function for a Partial match in Excel. In a similar way, you can use the wildcard characters with other conditional formulas for a partial match of conditions like using Using =IF() Function For Partial Match in Excel and many more.
Use of Special Characters to Find and Replace
The wildcard special characters are also used to find a text partially and/or replace it with some other text.
As you can see from the above image that there is inconsistency in the countries combination. For example India-USA has been written in different ways (IndiaUS, Indian-USA and so on).
To correct these, we can use the Find and Replace feature with the wildcard character to find all the cells that contain Ind and US and replace it with India-USA.
Follow the undermentioned steps:
Select the range of cells to which you want to apply the Find and Replace feature and use keyboard shortcut Ctrl+H to open the ‘Find and Replace’ dialog box.
In the ‘Find What’ section, write *Ind*US* to find all those cells in the selected range which contains the word Ind first and then US.
Note that *Ind*US* would not find for the cells which contain US first and then Ind.
In the ‘Replace With’ section, type the text that you want as a replacement i.e. India-USA.
Then click on the ‘Replace All’ button and as a result, excel would replace the cell containing the word Ind first and then US with the text India-USA.
With this we have come to the end of this amazing excel trick. Please share your views and comments in the comment section below. Your views and comments matter to us a lot.