How to Use Wildcards in Excel?

In this article, we shall see how to use wildcards in Excel.

Wildcard characters such as the asterisk “*”, question mark “?” and the tilde “~” can greatly extend searching, counting, and additional functionality in Excel.  Excel wildcards can be used with functions such as AVERAGEIF, SUMIF, COUNTIF, Excel database functions, and others.

The asterisk “*” is used to substitute any number of characters, whereas the question mark “?” is used to represent or substitute one character. The tilde “~” is used to identify a literal question mark or asterisk character in the text string itself.

What do I mean by all of this? Well, let’s get started with a simple example.

How to use wildcards in Excel

Every new parent welcomes the thought of choosing a name for their baby. There are various databases showcasing the popular baby name choices in different countries. According to a survey done by Babycenter, which surveyed nearly 400,000 parents, the most popular baby name choice for girls in 2016 was Sophia, whereas the most popular baby name choice for boys in 2016, was Jackson.

In our example, two hypothetical expectant parents have compiled a list of baby names based on their own preferences, and input received from friends and relatives. They are expecting a baby girl. It is getting closer to delivery time, so they have decided to go through their compiled list of preferred names and use wildcards in order to enhance their searching.

The source data is shown below:

Wildcards in Excel

1) The mother likes the sound of names that end in “anne” such as Joanne and wants to see how many are on the list currently. So the asterisk wildcard can be used in this case since she is not exactly sure how many other characters are in front of “anne”.

Read More: How to Generate Random Numbers in Excel (Ultimate Guide)

2) So in Cell D5, we enter the following formula using the asterisk wildcard:

=COUNTIF(A5:A22,”*anne”) and press CTRL – ENTER.

Read More: How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)

3) Three names meet the mother’s criteria and preference.

4) In order to see the actual names that match her criteria, we will use conditional formatting, in combination with wildcards to highlight these names.

5) Highlight the range and then go to Home> Styles> Conditional Formatting as shown.

6) Click on the Drop-down arrow next to Conditional Formatting and choose Highlight Cells Rules> Text that Contains.

7) In the Format cells that contain the text: textbox, enter *anne as shown below.

8) Click OK to see all the names that end in anne formatted in red as shown below.

Similar Readings

9) The father likes Isabelle or Isabella, which differ only by one letter, so to find this we do the following.

10) Firstly, we clear the current conditional formatting by choosing Home> Styles> Conditional Formatting> Clear Rules> Clear Rules From Entire Sheet as shown below, in order to remove the prior conditional formatting.

11) Highlight the range, again. Go to Home> Styles> Conditional Formatting> Highlight Cells Rules> Text that Contains and enter Isabell? in order to see Isabelle and Isabella highlighted. They differ by only a single character and thus the question mark wildcard is used. Click OK.

Read More: Search for Question Mark in Excel (4 Suitable Methods)

12) Now only Isabelle and Isabella, which differ by one single character are highlighted.

13) On the next sheet, we have the preferred names that the grandparents selected, denoted by an asterisk as shown below.

Read More: How to Remove Asterisk in Excel (5 Simple Methods)

14) We can thus count how many names out of the list the grandparents preferred. So in cell C5, we enter the formula:


The tilde, in this case, is denoting that the second asterisk is a literal part of the text string. The first asterisk, in this case, is a wildcard and is telling Excel to search for any number of characters before the actual asterisk.

15) Upon pressing CTRL-ENTER we get 3. So there were three names out of the list that the grandparents preferred.

16) In order to see the actual names using conditional formatting, we highlight the range again. Go to Home>Styles>Conditional Formatting>Highlight Cells Rules>Text that Contains and enter *~* and then Click OK.

17) The three names the grandparents preferred and denoted, using an actual asterisk in the text string is shown below

Read More: How to Find * Character Not as Wildcard in Excel (2 Methods)


Wildcards can greatly enhance Excel functions. They allow for partial match searching as well as partial criteria fulfillment.

Please feel free to comment and tell us how you use wildcards, which wildcard combinations have enhanced your searching, and baby name preferences in your region or country.

Download Working File

Related Readings

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply