Excel wildcard: find and replace, filter, use in formulas

Asterisk as a wildcard

The asterisk (*) is the most general wildcard character that can represent any number of characters. For example:

• ch* - matches any word that begins with "ch" such as Charles, check, chess, etc.
• *ch - substitutes any text string that ends with "ch" such as March, inch, fetch, etc.
• *ch* - represents any word that contains "ch" in any position such as Chad, headache, arch, etc.

Question mark as a wildcard

The question mark (?) represents any single character. It can help you get more specific when searching for a partial match. For example:

• ? - matches any entry containing one character, e.g. "a", "1", "-", etc.
• ?? - substitutes any two characters, e.g. "ab", "11", "a*", etc.
• ???-??? - represents any string containing 2 groups of 3 characters separated with a hyphen such as ABC-DEF, ABC-123, 111-222, etc.
• pri?e - matches price, pride, prize, and the like.

Tilde as a wildcard nullifier

The tilde (~) placed before a wildcard character cancels the effect of a wildcard and turns it into a literal asterisk (~*), a literal question mark (~?), or a literal tilde (~~). For example:

• *~? - finds any entry ending with question mark, e.g. What?, Anybody there?, etc.
• *~** - finds any data containing an asterisk, e.g. *1, *11*, 1-Mar-2020*, etc. In this case, the 1^st and 3^rd asterisks are wildcards, while the second one denotes a literal asterisk character.

How to search with wildcard

By default, the Find and Replace dialog is configured to look for the specified criteria anywhere in a cell, not to match the entire cell contents. For example, if you use "AA" as your search criteria, Excel will return all the entries containing it such as AA-01, 01-AA, 01-AA-02, and so on. That works great in most situations, but under certain circumstances can be a complication.

In the below dataset, supposing you want to find the IDs that consist of 4 characters separated with a hyphen. So, you open the Find and Replace dialog (Ctrl + F), type ??-?? in the Find what box, and press Find All. The result looks a bit perplexing, isn't it?

Technically, strings like AAB-01 or BB-002 also match the criteria because they do contain a ??-?? substring. To exclude these from the results, click the Options button, and check the Match entire cell contents box. Now, Excel will limit the results to only the ??-?? strings:

How to replace with wildcard

In case your data contains some fuzzy matches, wildcards can help you quickly locate and unify them.

In the screenshot below, you can see two spelling variations of the same city Homel and Gomel. We'd like to replace both with another version - Homyel. (And yes, all three spellings of my native city are correct and generally accepted :)

To replace partial matches, this is what you need to do:

1. Press Ctrl + H to open the Replace tab of the Find and Replace dialog.
2. In the Find what box, type the wildcard expression: ?omel
3. In the Replace with box, type the replacement text: Homyel
4. Click the Replace All button.

And observe the results:

How to find and replace wildcard characters

To find a character that Excel recognizes as a wildcard, i.e. a literal asterisk or question mark, include a tilde (~) in your search criteria. For example, to find all the entries containing asterisks, type ~* in the Find what box:

If you'd like to replace the asterisks with something else, switch to the Replace tab and type the character of interest in the Replace with box. To remove all the found asterisk characters, leave the Replace with box empty, and click Replace all.

Excel COUNTIF wildcard formula

Let's say you wish to count the number of cells containing the text "AA" in the range A2:A12. There are three ways to accomplish this.

The easiest one is to include wildcard characters directly in the criteria argument:

=COUNTIF(A2:A12, "*AA*")

In practice, such "hardcoding" is not the best solution. If the criteria changes at a later point, you'll have to edit your formula every time.

Instead of typing the criteria in the formula, you can input it in some cell, say E1, and concatenate the cell reference with the wildcard characters. Your complete formula would be:

=COUNTIF(A2:A12,"*"&E1&"*")

Alternatively, you can input a wildcard string (*AA* in our example) in the criteria cell (E1) and include only the cell reference in the formula:

=COUNTIF(A2:A12, E1)

All three formulas will produce the same result, so which one to use is a matter of your personal preference.

Excel wildcard VLOOKUP formula

When you need to look for a value that does not have an exact match in the source data, you can use wildcard characters to find a partial match.

In this example, we are going to look up the IDs that start with specific characters, and return their prices from column B. To have it done, enter the unique parts of the targets IDs in cells D2, D3 and D4 and use this formula to get the results:

=VLOOKUP(D2&"*", $A$2:$B$12, 2, FALSE)

The above formula goes to E1, and due to the clever use of relative and absolute cells references it copies correctly to the below cells.

Find and Replace with wildcard number

In the screenshot below, we are using *4* for the search criteria to search for the cells containing the digit 4, and Excel finds both text strings and numbers:

Filter with wildcard number

Likewise, Excel's auto-filter has no problem with filtering numbers containing "4":

Why Excel wildcard not working with numbers in formulas

Wildcards with numbers in formulas is a different story. Using wildcard characters together with numbers (no matter whether you surround the number with wildcards or concatenate a cell reference) converts a numeric value into a text string. As the result, Excel fails to recognize a string in a range of numbers.

For instance, both of the below formulas count the number of strings containing "4" perfectly well:

=COUNTIF(A2:A12, "*4*" )

=COUNTIF(A2:A12, "*"&E1&"*" )

But neither can identify digit 4 within a number:

How to make wildcards work for numbers

The easiest solution is to convert numbers to text (for example, by using the Text to Columns feature) and then do a regular VLOOKUP, COUNTIF, MATCH, etc.

For instance, to get the count of cells that begin with the number in E1, the formula is:

=COUNTIF(B2:B12, E1&"*" )

In situation when this approach is not practically acceptable, you will have to work out your own formula for each specific case. Alas, a generic solution does not exist :( Below, you will find a couple of examples.

Example 1. Excel wildcard formula for numbers

This example shows how to count numbers that contain a specific digit. In the sample table below, suppose you want to calculate how many numbers in the range B2:B12 contain "4". Here's the formula to use:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))

How this formula works

Working from the inside out, here's what the formula does:

The SEARCH function looks for the specified digit in every cell of the range and returns its position, a #VALUE error if not found. Its output is the following array:

{#VALUE!;1;#VALUE!;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}

The ISNUMBER function takes it from there and changes any number to TRUE and error to FALSE:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

A double unary operator (--) coerces TRUE and FALSE to 1 and 0, respectively:

{0;1;0;0;1;0;0;1;0;0;0}

Finally, the SUMPRODUCT function adds up the 1's and returns the count.

Example 2. Wildcard formula for dates

The SUMPRODUCT formula discussed above works beautifully for numbers but will fail for dates. Why? Because internally Excel stores dates as serial numbers, and the formula would process those numbers, not the dates displayed in cells.

To overcome this obstacle, utilize the TEXT function to convert dates to text strings, and then feed the strings to the SEARCH function.

Depending on exactly what you aim to count, the text formats may vary.

To count all the dates in C2:C12 that have "4" in the day, month or year, use "mmddyyyy":

=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "mmddyyyy")))))

To count only the days that contain "4" ignoring months and years, use the "dd" text format:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "dd")))))