Excel Wildcard Characters – An Introduction

July 27, 2019
Formulas

                       Wildcards are special characters that can take any place of any character (hence the name – wildcard). There are only 3 Excel wildcard characters (asterisk, question mark, and tilde).

The following wildcard characters can be used as comparison criteria for text filters, and when searching and replacing content.

* (asterisk)

                It represents any number of characters. For example, Ex* could mean Excel, Excels, Example, Expert, etc.

? (question mark)

                It represents one single character. For example, Tr?mp could mean Trump or Tramp.

~ (tilde)

                It is used to identify a wildcard character (~, *, ?) in the text. For example, let’s say you want to find the exact phrase Excel* in a list. If you use Excel* as the search string, it would give you any word that has Excel at the beginning followed by any number of characters (such as Excel, Excels, Excellent). To specifically look for excel*, we need to use ~. So our search string would be excel~*. Here, the presence of ~ ensures that excel reads the following character as is, and not as a wildcard.

Filter Data using Wildcard Characters

                Excel wildcard characters come in handy when you have huge data sets and you want to filter data based on a condition. Suppose you have a dataset as below. You can use the asterisk (*) wildcard character in data filter to get a list of companies that start with the alphabet A.

Here is how to do this:

  1. Select the cells that you want to filter.
  2. Go to Data –> Sort and Filter –> Filter (Keyboard Shortcut – Control + Shift + L).
  3. Click on the filter icon in the header cell
  4. In the field (below the Text Filter option), type A*
  5. Click OK.

This will instantly filter the results and give you 3 names – ABC Ltd., Amazon.com, and Apple Stores.

 How does it work

       When you add an asterisk (*) after A, Excel would filter anything that starts with A. This is because an asterisk (being an Excel wildcard character) can represent any number of characters. Now with the same methodology, you can use various criteria to filter results.

If you use A?C instead, you will only get ABC Ltd as the result (as only one character is allowed between ‘a’ and ‘c’)

Wildcard Characters & VLOOKUP

            Partial look-up is needed when you have to look for a value in a list and there isn’t an exact match. For example, suppose you have a data set as shown below, and you want to look for the company ABC in a list, but the list has ABC Ltd instead of ABC.

                  You cannot use the regular VLOOKUP function in this case as the lookup value does not have an exact match. If you use VLOOKUP with an approximate match, it will give you the wrong results. However, you can use a wildcard character within VLOOKUP function to get the right results:

Enter the following formula in cell D2 and drag it for other cells:

=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE)

How does this formula work

            In the above formula, instead of using the lookup value as is, it is flanked on both sides with the Excel wildcard character asterisk (*) – “*”&C2&”*”. This tells excel that it needs to look for any text that contains the word in C2. It could have any number of characters before or after the text in C2. Hence, the formula looks for a match, and as soon as it gets a match, it returns that value.

Find and Replace

              You have data as below, the region has been entered in different ways (such as North-West, North West, NorthWest).

To clean this data and make it consistent, we can use Find and Replace with Excel wildcard characters.

Here is how to do this:

  1. Select the data where you want to find and replace text.
  2. Go to Home –> Find & Select –> Go To. This will open the Find and Replace dialogue box. (You can also use the keyboard shortcut – Control + H).
  3. Enter the following text in the find and replace dialogue box:
  4. Find what: North*W*
  5. Replace with: North-West
  6. Click on Replace All.

This will instantly change all the different formats and make it consistent to North-West.

How does this Work?

           In the Find field, we have used North*W* which will find any text that has the word North and contains the alphabet ‘W’ anywhere after it. Hence, it covers all the scenarios (NorthWest, North West, and North-West). Find and Replace finds all these instances and changes it to North-West and makes it consistent.