Microsoft Excel

7 minute read

How to Use Wildcard Characters in Excel

Agnes Lo

Agnes Lo

What are wildcard characters in Excel?

Wildcard characters in Excel are special characters that can be used to take the place of characters in a formula. They are employed in Excel formulas for incomplete matches.

Excel supports wildcard characters in formulas to return values that share the same pattern. The characters are used to look for a text string with the same known patterns — the beginning and the ending characters, and also, the number of characters presented in the cell.

Wildcard characters are useful especially when the exact cell content is not known. It’s important to note that they only work with text, not numbers.

Learn the best Excel shortcuts

Step up your Excel game with this free print-ready cheatsheet

Types of wildcard characters

 There are three wildcard characters: 

* (asterisk)

(question mark)

~ (tilde)



Wildcard characters


Asterisk

* (asterisk) represents any number of characters. For example “Aus*” returns Australia or Austria

asterisk wildcard character - country list

Wildcard characters

Question mark

? (question mark) represents one single character. For example, “p?n” returns pan, pen or pin.

wildcard characters question mark - letter p

Wildcard characters

Tilde

~ (tilde) is used to literally indicate the asterisk and question mark characters as they are, as * or ?, instead of a wildcard character in the formula. For example, “Aus~*” returns Aus* but not Australia or Austria like above.

wildcard characters tilde - list of countries

Wildcard characters

Wildcard characters

How to use wildcard characters

Wildcard characters are commonly used in some basic Excel formulas, i.e., COUNTIF, COUNTIFS, VLOOKUP, FIND AND REPLACE, SEARCH, CONDITIONAL FORMATTING, etc.

Here are some examples of how it works:

1. VLOOKUP

In a normal circumstance, VLOOKUP looks up the exact value specified in a list and returns the corresponding value in a table.

Sometimes, the exact, full value in the list is not available and only the partial content is known. This is where wildcard characters come in handy.

In the example below, the task is to find out the number of delivery points for Austin. But instead of Austin, the list displays Austin, Texas.  

The usual VLOOKUP formula wouldn’t work in this scenario because the lookup value – Austin, does not have the exact match.

Wildcard characters To find the information, enter: 

=VLOOKUP(D3&"*";A1:B9;2;FALSE)

By inserting “*”, it tells Excel to look for any text that begins or ends with the lookup value in D3 – Austin, and it may have any number of characters after the text.

It finds an exact match, which is 5 in this case. Wildcard characters

2. FIND AND REPLACE

Using wildcard characters in Excel find and replace is useful to correct data and make the data set consistent throughout the database.

This situation is particularly common when handling data entered manually, either by staff or customers, creating extra values in the database and could make analysis difficult.

The example below shows a common picture when handling a client’s database.

Wildcard charactersThe cities under Washington DC could be confusing.

To rectify, use the simple “FIND AND REPLACE” function in Excel.

  • Find Washington with a D after it and replace it as “Washington DC” 

Wildcard characters

  • Click “Replace All” and now all the “Washington DC” values are aligned, resulting in a clean database. 

Wildcard characters

3. COUNTIF

To include wildcard characters as part of the COUNTIF function counts the number of cells in the given range that meets the specified number of characters.

  • i.e., there’s a list of country codes, and the task is to count the number of countries with three characters in their respective country code.

In this scenario, each ? refers to one character. Wildcard charactersSince we would like to find out the country with three characters in their country code, the formula should include three ???

To find out, enter: 

=COUNTIF(A2:A9;"???")

The return value is 2. Wildcard characters

4. CONDITIONAL FORMATTING

In addition to formulas, wildcard characters can be used to create conditional formatting as well. 

Say, the task is to highlight the countries with names that begin with the letter G

conditional formatting

  1. Go to Conditional Formatting > New Rule > Select “Use a formula to determine which cells to format.”
  2. Input the formula below. 

The idea is to find countries with names that begin with the letter G. So * (asterisk) is used to request Excel to return values that begin with G and are followed by any number of characters.

  1. Select the desired formatting — in this case, font in red. 

conditional formatting - red

  1. Click Okay, then apply the same format to the rest of the cells under Column A by using Format Painter

The result is below:

conditional formatting applied

Excel wildcard not working? Here's why

Mentioned previously, the use of * (asterisk) and ? (question mark) refer to different situations. However, they don’t work if the wildcard characters themselves are part of the lookup value.

  • i.e., to find “Aus?”, “Aus~?” is required. 

Summary

Wildcard characters work best in situations where the given data is incomplete or partially available. They are used to take the place of characters in a formula, whether as any number of characters or a single character, expressed as * (asterisk) and ? (question mark). 

These characters are compatible with most common functions — IFS, VLOOKUP, FIND AND REPLACE and CONDITIONAL FORMATTING. They help search, match and return a partial match in situations where the lookup value is incomplete. They sometimes rectify data by synchronizing them as well.

As powerful as it might be, it’s key to note that they only work with text, but not numbers. 

To learn more about Excel formulas and functions, try our bite-sized Excel courses today.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial
Agnes Lo

Agnes Lo

Agnes is a clienteling professional in luxury retail. She enjoys yoga and outdoor activities in her free time.

1 comment

LoginSign up
  • Tavia

    Awesome. 💯