Wildcard Characters in Excel

If you’re an Excel user, you’ve probably heard of wildcards.

Wildcards are special characters that allow you to perform “fuzzy” matching on text in your formulas.

You can use them to find and replace text, match patterns, and more. In this section, we’ll cover the available wildcards, for example, wildcard usage and functions that support wildcards.

Available Wildcards in Excel

Excel has three wildcards you can use in your formulas:

  • Asterisk (*)
  • Question mark (?)
  • Tilde (~)

The asterisk represents zero, one, or more characters, the question mark represents any single character, and the tilde is used as an escape character for literal characters such as ~* for a literal asterisk, ~? for a literal question mark, or ~~ for a literal tilde.

Note that wildcards only work with text, not numbers.

Example Wildcard Usage

Here are some examples of how you can use wildcards in your formulas:

UsageBehaviorWill Match
?Any one character“A”, “B”, “c”, “z”, etc.
??Any two characters“AA”, “AZ”, “zz”, etc.
???Any three characters“Jet”, “AAA”, “ccc”, etc.
*Any characters“apple”, “APPLE”, “A100”, etc.
*thEnds in “th”“bath”, “fourth”, etc.
c*Starts with “c”“Cat”, “CAB”, “cindy”, “candy”, etc.
?*At least one character“a”, “b”, “ab”, “ABCD”, etc.
???-??5 characters with hyphen“ABC-99″,”100-ZT”, etc.
*~?Ends with a question mark“Hello?”, “Anybody home?”, etc.
*xyz*Contains “xyz”“code is XYZ”, “100-XYZ”, “XyZ90”, etc.

Remember that wildcards only work with text. For numeric data, you can use logical operators.

Functions that Support Wildcards

Not all functions allow wildcards, but here is a list of the most common functions that do:

  • AVERAGEIF, AVERAGEIFS
  • COUNTIF, COUNTIFS
  • MATCH
  • MAXIFS, MINIFS
  • SEARCH
  • SUMIF, SUMIFS
  • VLOOKUP, HLOOKUP
  • XLOOKUP
  • XMATCH

By using wildcards, you can make your Excel formulas more powerful and flexible. Whether you’re finding and replacing text or matching patterns, wildcards can help you get the job done.

Let me show you some examples on how to use Wildcards in Excel

Wildcard Characters Examples in Excel

Let’s now have a look at some examples of using Wild Characters in Excel and how it can help you be more efficient in your day-to-day work.

Use Wildcard Characters to Filter Data

Below, I have a dataset where I have the product codes in column A and the sales values in column B.

Dataset for using wildcards

Now, assume that I want to filter product codes that start with the letter “A”.

To do that, I can follow the below simple steps.

  1. Click on the filter icon in the “Product code” header. So, click the filter icon on cell A1.
Click on the filter icon
  1. Then, type “A*” in the filtering field.
type in the filtering field
  1. Click “OK”.
Click OK

Now, I have filtered product codes that start with the letter “A”.

filtered product codes that start with the letter A

If you have not used the asterisk sign, then Excel filters all product codes that contain the letter “A” anywhere of the product code. So, you’ll also get “SA-1036” in the filtered list.

In this case, the “Asterisk” sign (*) after the letter A helped you to filter product codes that start with the letter “A”. The Asterisk sign represents any number of characters.

But, if you want to specify the number of characters, you can use the question marks instead of the asterisk. Each question mark represents any single character.

Below, I have a dataset where I have the product codes in column A and the sales quantity in column B.

dataset with product codes

Now, I want to filter product code with four characters.

To do that, I can easily use wildcards for filtering.

  1. Click on the filter icon in the “Product code” header. So, click the filter icon on cell A1.
Click on the filter icon
  1. Type “????” (four question marks) in the filtering field. If you want to filter codes with five characters, you have to type five question marks because each question mark represents one character.
Type four question marks in the filtering field
  1. Click “OK”.
Click OK to filter

Now, you have filtered product codes with four characters.

filtered product codes with four characters

Sometimes, we want to filter items that contain an asterisk (*) or question mark (?).

For example, if you want to filter items with an asterisk sign, you can’t just enter the asterisk sign to filter the data. Let’s look at the below example.

Below, I have a dataset where I have the product codes in column A and the sales quantity in column B. An asterisk is used at the end of the product codes to identify high-value products.

dataset of product codes

Now, I want to filter high-value products.

To do that, I have to follow the steps below.

  1. Click on the filter icon in the “Product code” header. So, click the filter icon on cell A1.
Click filter icon in Product code header
  1. Type “~*” (A tilde followed by an asterisk) in the filtering field. When I use the tilde mark (~), Excel reads the next character as it is and not as a wildcard character. So, if I want to filter items with a question mark, I have to use “~?”.
Enter tilde followed by an asterisk
  1. Click “OK”.
Click ok to filter

Now, you have filtered product codes with an asterisk sign.

filtered product codes with an asterisk sign

Wildcard Characters to Find and Replace Partial Matches

You can also use Excel wildcard characters to Find and Replace.

Below, I have a dataset where I have the names in column A and the country in column B.

names in column A and country in column B

You can notice that I have written “the United States” with different abbreviations. Now, I want you to keep one abbreviation format for all records.

I can follow the below steps for that.

  1. Select the Country column. So, I select column B data.
Different ways of writing US
  1. Press “Ctrl + H” to open the “Find and Replace” dialog box.
  2. Enter “U*” on the “Find What” box.
open the Find and Replace dialog box
  1. Enter the preferred abbreviation for the United States in the “Replace with” box. So, I enter “USA” in the “Replace
Enter USA in replace with field
  1. Click “Replace All”.
Click on Replace All

Now, you can see all the abbreviations of the United States in the same format.

abbreviations of the United States in the same format

Using Wildcard Characters Inside Excel Functions

You can use the wildcard characters when you want to do partial matches, such as  XLOOKUP, VLOOKUP, SUMIF, and many other functions.

Below, I have a dataset where I have the product codes in column A and the sales values in column B.

Dataset for using wildcards

Now, I want to find the total sales product codes that start with the letter “A”.

For that, I can use the below formula.

=SUMIF(A2:A6,"A*",B2:B6)
total sales product codes start with letter A

In the above formula, I have used the wildcard – asterisk (*) to create a condition with a partial match.

The XLOOKUP function has the ability to do a lookup based on wildcard characters.

Below, I have a dataset where I have the product codes in column A and the sales quantity in column B.

dataset with product codes

Now, I want to get the product code that starts with the letter “A” and has 3 characters only.

To do that, I can use the below XLOOKUP formula.

=XLOOKUP("A??",A2:A6,A2:B6,,2)
XLOOKUP formula with wildcard

In the above formula, I have used “A??” for the lookup_value argument. It helps me to look up codes that have only three characters and also start with the letter A.

Whenever you are using wildcard characters in your XLOOKUP formula, you have to set the match mode argument to number 2.

Using Wildcard Characters with Conditional Formatting

Sometimes, we have to use wildcards to apply conditional formatting.

Below, I have a dataset where I have the product codes in column A and the sales quantity in column B. An asterisk is used at the end of the product codes to identify high-value products.

dataset of product codes

Now, I want to apply conditional formatting to highlight high-value product rows.

For that, I can follow the below steps.

  1. Select the entire data table without headers.
conditional formatting to highlight high-value product rows
  1. Go to the “Home” tab.
Click on Home
  1. Go to the “Styles” group and expand the “Conditional Formatting” options. Then, click “New Rule…”.
Click on New rule

You’ll get the “New Formatting Rule” dialog box.

  1. Select “Use a formula to determine which cells to format”.
New Formatting Rule dialog box
  1. Enter the below formula as the rule.
=ISNUMBER(SEARCH("~*",$A2))
Enter formula in Conditional formatting
  1. Click the Format button and select the way you want to format cells that meet the given criteria.
Click on Format
  1. Click the “OK” button.

Now, I have highlighted all the high-value product rows in my table.

rows highlighted

In this article, I covered everything you need to know about wild card characters, including some practical examples of using them in Excel spreadsheets.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.