Wildcard In Excel

What Is Wildcard In Excel?

A wildcard is a special character that replaces any character or any number of characters of a value in a cell. A wildcard is very helpful in matching values even though they are not the same as the original value in the cell.

For instance, if we have Tim Edmund in one cell and Tim in another cell, we can still match Tim to Tim Edmund by using wildcard in excel. Hence, we can call wildcards as partial matching supporters in Excel. The advantage of using wildcards in Excel is when we do the lookup and any text functions. The moment we are not sure of the exact data or only a partial element of the data is available, that is when wildcards will be flexible and versatile to support our matching of data in Excel.

For example, look at the following data in Excel.

We have city1 and city2 in cells A2 and C2, respectively. We need to get the value from the City1 table based on the City2 value in cell C2 and then apply the VLOOKUP function.

Since both the values are different, the result is #N/A. However, by inserting wildcard in excel formula, such as asterisk along with the City2 name, we will get value from the City1 table as shown in the below image.

Here, the wildcard asterisk (*) matched the remaining number of characters in cell C2 and retrieved the value from the City1 table.

Types Of Excel Wildcard

Three types of wildcards can be used in Excel. They are:

1. Asterisk (*)
2. Question Mark (?)
3. Tilde (~)

ASTERISK (*): It is one of the common and most used wildcard characters in Excel. Asterisk (*) matches any number of characters in a word.

• Ex* : It can match the words that start with the characters ‘Ex’ and ends with any number of characters. E.g., Excellent, Excel, Experience, Experiment.
• *Ex : It can match the words that end with the characters ‘Ex’ and starts with any number of characters. E.g., Codex, FedEx, Duplex, Vertex, Complex.
• *Ex* : It can match words that contain the character ‘Ex.’ Starting and ending of the words don’t matter here. E.g., Text, Lexes, Lexis, Dex.

QUESTION MARK (?): This wildcard represents any single character in the word.

• P?t : It can match the words that start with the word ‘P’ and ends with the word ‘t.’ For example, PAT, PUT, PET, and POT.
• ??-?? : It will match the string of words, i.e., ABC-CFF, DC-PC, A@#-S@#.

TILDE (~): This wildcard is used to find the wildcard characters in the text. Tilde (~) is used to cancel the wildcard presence.

• For instance, assume we have the words Excellent and Excel in the worksheet. If we need to find the exact word ‘Excel’, we generally put in the asterisk (*) wildcard character to find the partial match, i.e., ‘Excel*’. However, this ends up searching both the words ‘Excellent’ and ‘Excel’. To cancel the presence of the wildcard character asterisk (*), we can apply the wildcard character tilde (~) as ‘Excel~*.’

It will cancel the presence of the wildcard asterisk (*) and ends up searching only Excel in the list.

How To Use Wildcard In Excel?

Users make use of wildcard in excel commonly. Let us have a look at the following examples to understand wildcards.

Example 1 – Find And Replace Using Question Mark Wildcard (?)

Assume we have collected the data from the employees about the technical training they need, and most of them sent the training name as ‘Excel Tutorial’. However, as shown in the following Excel spreadsheet, we got 5 different types of Excel Tutorials as inputs.

After the word Excel, we need to have space, but instead, we have various characters. So let us use question mark (?) wildcard to replace them with space.

The steps used to replace value using wildcard in excel are as follows:

Step 1: Select the data range from the range A2:A6.

Step 2: Press the shortcut keys, Ctrl + F, to open the following Find and Replace window.

Step 3: In the Find What: dialogue box, enter the word ‘Excel?Tutorial.’

Step 4: Click on Find All.

It will showcase all the cells that contain the word with the mentioned pattern.

As we can see from the above image, there are 5 cells with the same pattern.

Now we have to remove the unwanted characters between the words ‘Excel’ and ‘Tutorial’ and replace them with a space.

Step 5: Click on the Replace tab in the same Find and Replace window.

Step 6: In the Replace with: box, enter the word Excel Tutorial. It is the word that we need to replace with.

Step 7: Click on Replace All, and all the characters will be replaced with a space character.

Excel has made 5 replacements, and now we have the common value as ‘Excel Tutorial.’

Likewise, we can use wildcard in excel function to replace values in the text.

Example 2 – Conditional Formatting Using Wildcard Characters

Wildcards can be used to apply conditional formatting in Excel. For instance, we have a list of cities in Excel.

From the above list of cities, we need to highlight the cities that begin with the letter ‘N’.

Let us learn the use of wildcard in excel with the below steps.

Step 1: Select the range of cells from the range A2:A10.

Step 2: Go to the Home tab, and under Conditional Formatting, click on New Rule… option.

Step 3: Choose Use a formula to determine which cells to format option in the New Formatting Rule window.

Step 4: Enter the following formula in Format values where this formula is true: box.

Step 5: Click the Format option in the same window.

Step 6: In the Format Cells window, click on the Fill tab and choose the suitable color to highlight the city name that starts with the word ‘N’.

Step 7: Click on OK in the next two windows. Excel highlights the city names starting with the word ‘N’, indicating that conditional formatting is ready.

Explanation of the Formula: The formula that we have applied is as follows:

=IF(COUNTIF(A2,”N*”),TRUE,FALSE)

COUNTIF Excel function will test whether the value in cell A2 (ideally from A2 to A10) starts with the word ‘N’ and the wildcard character asterisk (*) will match the remaining number of characters. If that is TRUE, then COUNTIF will return 1, or else, it will return FALSE.

If the value is TRUE, then conditional formatting will be applied with fill color, or else, no formatting will be applied.

Example 3 – Filter Values Using Wildcards

Wildcards can be used in filtering the data as well. For instance, we have the following invoice list in an Excel spreadsheet.

From the data, suppose we want to filter the invoice numbers that start with ‘L’. To filter, we should follow the below steps.

Step 1: Select the entire data range and insert the auto filter using the shortcut keys, Ctrl + Shift + L.

Step 2: Click on the drop-down icon of the Invoice # column to open the filter list.

Step 3: In the search box, enter the filtering criteria as ‘L*.’

Step 4: Click OK, and we will have the list of invoices filtered, beginning with the letter L.

Explanation: Here, the criteria given is ‘L*’, the letter ‘L’ and the wildcard character asterisk (*) match any number of characters after the word ‘L’. Hence, it filters all the records which begin with the word ‘L’.

Similarly, let us apply the question mark (?) wildcard in the search box.

We have given the criteria as 7 question marks, and in return, it returns all the records with the 7 letters.

In the above list of invoices, we also have the wildcard character asterisk. So now, we will try to filter out the records with the wildcard character asterisk (*). To do this, in the search box, enter the criteria as shown below.

It will filter the records which have an asterisk (*), as shown in the image below.

Explanation: In the criteria, we have provided ‘~*’, tilde wildcard searches for the records with the wildcard character asterisk (*).

Similarly, if we give the criteria as ‘~?’, tilde will search for the records with the wildcard question mark (?).

Excel Formulas That Support Excel Wildcards

Excel wildcards can also be used in the formulas as well. The following formulas will support wildcards in their functions.

• VLOOKUP
• COUNTIF and COUNTIFS
• SUMIF and SUMIFS
• MATCH Excel Function
• AVERAGEIF
• IF Excel Function
• SEARCH

These are the important functions that support wildcards.

Now, let us have a look at some examples.

Partial Match in VLOOKUP: For instance, consider the following data with employee age in column A and their age in column B, respectively. Let us understand partial match VLOOKUP with wildcard in excel.

In cell E2, we are trying to find the age of the employee William. However, in the actual data range A2:A7, we have the real name William Ken in cell A2.

We will use an asterisk (*) wildcard to do the partial match up.

The logic “*”&D2&“*” will search for William with any number of characters on either side.

COUNTIF: For instance, with the same data, we will try to find the count of employees whose name starts with ‘Andy’.

Criteria “D6&*” will search for the employee whose name starts with Andy and return the count of it. Since the table lists two employees named ‘Andy’ in cells A4 and A7, the function has returned 2.

In this way, we can use wildcard characters in Excel formulas.

Important Things To Note

• Not all Excel functions support Excel wildcards; those that support wildcards are VLOOKUP, HLOOKUP, IF, AVERAGEIF, COUNTIF, COUNTIFS, SUMIF, and SUMIFS.
• We should be careful when we use wildcards to do partial matches as it considers the values with the same data as one. For instance, the combination “*William*” treats William Ken and Robert William as one.
• Question mark (?) matches any single position of the text value.
• Tilde (~) is used to find wildcard characters like an asterisk (*) and question mark (?).
• Excel Advanced filter also supports wildcard matches.

Frequently Asked Questions

Download Template

This article must be helpful to understand the Wildcard In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Wildcard In Excel. Here we discuss how to use the three types of wildcards with examples and downloadable excel template. You can learn more from the following articles – 

• VLOOKUP to the Left
• XLOOKUP
• Alternatives to VLOOKUP In Excel