Quick Links
Wildcards in Microsoft Excel let you search for partial matches, broaden your filters, and create formulas that reference cells containing certain strings. They represent non-specified characters to help you locate text values with “fuzzy” matches.
The Wildcards: Asterisk (*) and Question Mark (?)
There are two wildcards in Excel, and knowing their purpose is crucial to understanding how wildcards work overall.
The Asterisk Wildcard: Any Number of Characters
The first of the two wildcards in Microsoft Excel is an asterisk, which represents any number of characters, including no characters.
Criterion: OK
Criterion: *OK
No match
The Question Mark Wildcard: Any Single Character
Microsoft Excel’s second wildcard is the question mark, which substitutes any single character.
Criterion: ?OK?
Criterion: ?OK
Match
Question Mark and Asterisk Wildcards Combined
You can also use the question mark and asterisk together to find results that have finite numbers of characters in some positions, but any number of characters in others.
For example,
Criterion: ??OK*
Criterion: *OK?
Criterion: ?OK*
Canceling Wildcards: Tilde (~)
Sometimes, you might actually want to search for question marks and asterisks as characters in their own right in your Excel worksheet. This is where the third wildcard character—the tilde—comes into play: simply place it before the question mark or asterisk to tell Excel you don’t want them to be treated as wildcards.
???k
Using Wildcards in Searches
One of the most common uses of wildcards in Microsoft Excel is to find characters in a workbook and, if needed, replace them with alternatives.
Find Character Strings
In this list of product codes, the letters at the beginning represent where the product was manufactured (AUS for Australia, UK for the United Kingdom, USA for the United States, and CAN for Canada), the first digit represents the relevant store department (1 is clothing, 2 is homeware, 3 is sports, and 4 is garden), and codes with a letter at the end are national sale only (A) or international sale only (B).
Let’s say you want to find all the homeware products that are for national sale only.
Press Ctrl+F to launch the Find tab of the Find And Replace dialog box, and in the Find What field, type:
where
Then, when you click “Find All,” you’ll see a list at the bottom of the Find And Replace dialog box that shows any results that match these criteria.
Click any of the results in the Find And Replace dialog box to jump to the corresponding cell in your spreadsheet.
Now, let’s say your aim is to return the codes for all clothing products that aren’t restricted to national or international sales only (in other words, codes that contain a country and a three-digit number starting with 1, but don’t end with a letter).
In the Find What field, type:
However, when you click “Find All,” a product ending in a letter shows up in the result, suggesting there’s something missing from the criteria.
In fact, this happened becausepartof the erroneous code contains the substring you entered for the query. So, to exclude these and return only the codes thatfullymatch your query, click “Options,” check “Match Entire Cell Contents,” and click “Find All” again.
This time, only the desired results are displayed.
Find And Replace Character Strings
This Excel list shows respondents’ all-time favorite soccer players. However, Maradona’s name has been spelled in three different ways, and you want to amend the incorrect ones.
First, press Ctrl+H to launch the Replace tab of the Find And Replace dialog box. Then, in the Find What field, type:
since all incorrect spellings start with these three letters, and it’s the only name in the list that starts with this character string.
and click “Replace All” to confirm the corrections.
Now, all cases of this player’s name are spelled correctly and consistently.
Using Wildcards in Formulas
As well as using wildcards in Excel’s Find And Replace dialog box, you can use them in arguments for various functions, such asXLOOKUPto generate a partial match lookup,COUNTIFSto count the number of cells that match multiple criteria, and many more.
In this example, you want to use the SUMIF function to work out the total price of products originating in the UK.
into a blank cell, where
Wildcards and text values must always be placed in quotation marks in Excel formulas.
However, suppose you then want to find the total price of products from another country. In the example above, you would need to change the formula manually, a method that takes time and is more likely to result in error.
Instead, type the criteria (in this case, “UK”) in a separate cell. Then, reference that cell in the formula, using the ampersand (&) symbol to separate the cell reference from the wildcard embedded in quotation marks:
Using Wildcards in Filters
Another use of wildcards in Microsoft Excel is in filters.
Whether you’re working in a formatted Excel table or an unformatted range, select any cell in the array, and press Ctrl+Shift+L to add filter buttons.
Now, let’s say you want to filter the product codes to include only those that contain the letter A at the end.
To do this, click the Product_Code filter button, and in the Search field, type:
to include all codes ending in the letter A. Since the asterisk wildcard is placed before the letter A in the search, it doesn’t pick up any product codes containing A at the start (such as AUS458) or in the middle (such as USA320B).
Once you’ve previewed the results in the filter pop-up window, click “OK,” and see your data refined to match your criteria.
Points to Note
Before you go ahead and make use of wildcards in your Excel spreadsheet, take a moment to read these final pointers:
As well as using wildcards to broaden your search capabilities in Excel, you’re able to also use them tofind and replace text in Microsoft Word. The major difference between the two is that, alongside the asterisk and question mark, Word also lets you use square brackets ([ ]) as wildcards to match more than one item.
