Excel Wildcard (Table of Contents)
Introduction to Wildcard in Excel
The wildcard characters in excel are the most underrated feature of excel and most of the people don’t know about it. It is very good to know feature as it can save a lot of time and effort required to do some research in excel. We will learn about excel wildcard characters in detail in this article.
There are 3 Wildcard Characters in Excel:
- Asterisk (*)
- Question Mark (?)
- Tilde (~)
These three wildcard characters definitely have a different purpose from each other.
1. Asterisk (*) – The Asterisk represents any number of characters in the text string.
For Example, when you type Br*, it could mean Break, Broke, Broken. So * after Br could mean to select only words which start with Break it doesn’t matter what words or number of characters are thereafter Br. You can similarly start the search with Asterisk.
For Example, *ing, it could mean Starting, Ending, Beginning. So * before ing could mean to select only words which end with ‘ing’ and it doesn’t matter what words or number of characters are there before ing.
2. Question Mark (?) – The Question marks are used for a single character.
For Example,?ove could mean Dove or Move. So here question mark represents a single character which is M or D.
3. Tilde (~) – We have already seen two wild characters Asterisk (*) and Question Mark (?). The third wildcard character which is Tilde (~) is used to identify the wildcard character. We have not come across many situations where we need to use tilde (~) but it’s good to know feature in excel.
How to Use Wildcard Characters in Excel?
Let’s now look at the below examples to use wildcard characters in Excel.
Example #1 – Filtering Data with Wildcard Characters
Suppose you are working on the sales data where you have customer name, customer address and sales amount as given in the below screenshot. In the customer list, you have different companies of the parent company “prem enterprise”. So if I need to filter all the companies of Prem in the customer name I will just filter with one Asterisk behind Prem and one Asterisk after prem which will look like “*Prem*” and the search option will give me the list of all companies which has Prem in the company name.
Follow the below steps to search and filter the companies which have “Prem” in its name.
- Go to the Data tab in Excel.
- Click on the Filter option.
- Once the filter is applied, go to column A “Customer Name” and click on the drop-down box.
- In the search field, type “*Prem*” and click on OK.
- As you can see, all the three companies which have “Prem” in its name has been filtered and selected.
Example #2 – Find and Replace Using Wildcard Character
The area where we can use the wildcard characters effectively to find and replace words in Excel. Let us take a similar example of what we used in Example 1.
In the first example, we filtered the name of the companies which has “Prem” in its name. So in this example, we will try to find the name of the companies which has “prem” in its name and replace the name of the company with the name “Prem Group of Companies”. So for doing this, you need to follow the below steps.
- Press CTRL + H in the Excel and you will see the below screen open.
- In the Find what field, enter the word “*Prem*“. So that it should search the name of the companies which has “Prem” in its name.
- In the Replace with option, Enter the word “Prem Group of Companies”.
- Now click on “Replace All” button so that it will replace all the name of the companies which has “prem” in its name with “Prem Group of Companies”.
- After clicking on the “Replace All” button, we will get the below dialog box.
You can see that the name of the companies in row 3, 7 & 8 is changed to “Prem Group of Companies”.
Example #3 – Vlookup Using Wildcard Character
Just like we used to find and replace with the help of wildcard character, we can also use wildcard characters in excel Vlookup as well. We will take a similar example of example 1. But in addition to the data in example 1, we have a table with an initial reference of company name in column E.
The normal lookup will not work here as the names are not similar in column A & F. So we need to give a lookup in a way so that it should pick up the result for the company names in column A.
Follow the below steps to see how we can do that.
- Enter the formula for Vlookup in column F2 as shown in the below screenshot.
- Start the Vlookup value with an asterisk in between the semicolons “*” as shown in the below screenshot.
- Now type “&” to connect the reference with cell E2.
- Give the reference for cell E2.
- Again type “&” after the cell reference.
- Now end the Vlookup value with an asterisk in between the semicolons “*” as shown in the below screenshot.
- Now for the Table_Array in vlookup give the reference of column A so that it should pick up the value from Column A.
- In the column Index, you can select 1 as we need the value from column A itself.
- For the last condition in VLOOKUP formula, you can select FALSE for the Exact match.
- Press Enter key.
- Now you can drag down the formula to the below row and see the result.
As you can see the lookup has picked up the value from column A even though the names were not exactly the same in column F.
Things to Remember About Wildcard in Excel
- The wildcard characters need to be used carefully because it can give the result of other option which can arrive with the same logic. You may need to check on those options. For example in example 3, where we lookup with the help of wild card character, there were 3 companies with “Prem” in its name but look captured only the first company name which has “prem” in its name in column A.
- Wildcard characters can be used in other functions of excel like Vlookup, count, match, etc. So you can use a wildcard character in many innovative ways.
- We can also use the combo of Asterisk (*) and question mark (?) in a few situations if required.
Recommended Articles
This is a guide to Wildcard in Excel. Here we discuss How to Use Wildcard Characters in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
