How to Use Wildcards in Excel (4 Easy Methods)

In this article, we will demonstrate how to use wildcards in Excel.

Wildcard characters such as the asterisk “*“, question mark “?” and tilde “~” can greatly extend searching, counting, and additional functionality of 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 substitute one character only, and the tilde “~” is used to identify a literal question mark or asterisk character in the text string.

Let’s use these wildcards in some examples.

How to Use Wildcards in Excel: 4 Easy Methods

In our example, two hypothetical expectant parents have compiled a list of baby names for the baby girl they are expecting.

We’ll use wildcards in order to enhance searching this list for suitable names.

Method 1 – Using the COUNTIF Function to Find Names Ending with a Specific Word

Here, we’ll use the COUNTIF function to find names ending with a specific word, “anne“. The COUNTIF function returns the number of results that match a certain condition in a dataset.

We will determine how many names that end in “anne“, such as Joanne, are in the list currently. We’ll use the asterisk wildcard since we are not exactly sure how many other characters are in front of “anne“.

Steps: 

• In cell E5, enter the following formula:

• Press CTRL + ENTER.

Three names meet the criteria.

Method 2 – Applying Conditional Formatting with Wildcards

Now we’ll use conditional formatting in combination with wildcards to find and highlight specific names by applying new rules.

Steps: 

• Select the range and go to Home > Styles > Conditional Formatting.

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

• In the Format cells that contain the text textbox, enter *anne.

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

To find Isabelle or Isabella, which differ only by one letter, do the following:

• Clear the current conditional formatting by selecting Home> Styles > Conditional Formatting > Clear Rules > Clear Rules From Entire Sheet.

• Select the range again.
• Go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter Isabell?.

Since the terms to be matched differ by only one character, we use the question mark wildcard.

• Click OK.

Only Isabelle and Isabella are highlighted.

Method 3 – Using the COUNTIF Function to Find Text Containing Asterisks

Now, similarly to Method 1, we’ll use the COUNTIF function to find names containing asterisks.

In the modified dataset below, some of the names have had an asterisk added.

Let’s count these names with asterisks.

Steps:

• In cell D5, enter the following formula:

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

• Press CTRL-ENTER

3 is returned, indicating that three names out of the list contain an asterisk.

Method 4 – Using Conditional Formatting to Find the Names Containing Asterisks

Let’s now find the names containing asterisks using Conditional Formatting.

Steps: 

• Go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Text that Contains.
• Enter *~* and click OK.

The three names containing an actual asterisk in the text string are highlighted.

Filtering With Wildcards

We can also make use of wildcards when filtering.

Steps: 

• Select the whole dataset.
• Go to the Data tab.
• In the Sort & Filter section, click on the Filter option.

• Click the icon that appears at the top right corner of the dataset label.

• The window shown below pops up.

• Type the required text to filter in the search box (here, anne).
• Click OK.

The names containing anne are returned.

Replace Data with a Wildcard

We can easily find and replace Excel wildcard data.

Steps: 

• Press Ctrl+H.

The Find & Replace pop-up window will appear.

• To replace Olivia with Watson, enter the name Olivia in the Find What box.
• Enter the name Watson in the Replace With box.
• Click on the Replace button.

The name Olivia will be replaced with Watson.

What to Do If Excel Wildcards are Not Working

Ensure there are no incorrect characters in the data. Also, ensure that the data doesn’t contain leading or trailing spaces, uneven use of straight and curly quote marks, nonprinting characters, or other errors while counting text values. COUNTIF might provide an unexpected result in certain circumstances.

Download Practice Workbook

<< Go Back to Wildcard in Excel | Learn Excel