Wildcard In Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Wildcard In Excel?

Wildcards in Excel are the special Excel characters that take the place of the characters in it. Excel has three wildcards: an asterisk, question mark, and tilde. Asterisk is used for multiple numbers of characters in Excel, while a question mark represents only a single character. In contrast, the tilde is the identification of the wild card character.

Wildcard characters are special characters used to find the result, which is less than exact or accurate.

For example, if you have the word "Simple Chat." In the database, you have "Simply Chat," then the common letter in these two words is "Chat," so we can match these with Excel wildcard characters.

Wildcard in Excel
Table of contents
  • Wildcards are special characters used in Excel that replace other characters in a formula. There are three wildcards in Excel: the asterisk, question mark, and tilde.
  • The asterisk symbol denotes the occurrence of zero or more characters, while the question mark symbol signifies the presence of only a single character. The tilde character, on the other hand, is used to denote a wildcard character, which can represent any character or set of characters.
  • Special characters known as wildcards are used to search for approximate results instead of exact matches.

Types Of Excel Wildcard

There are three types of wildcard characters in Excel.

#1 - Asterisk (*)

It is to match zero or the number of characters. So, for example, "Fi*" could match "Final, Fitting, Fill, Finch, and Fiasco," etc.

#2 - Question Mark (?)

It is used to match any single character. For example, "Fa? e" could match "Face" & "Fade," "?ore" could match "Bore" & "Core," "a? ide," which could match "Abide" and "Aside."

#3 - Tilde (~)

It is used to match wildcard characters in the word. So, for example, if you have the word "Hello*" to find this word, we need to frame the sentence as "Hello~*." So here, the character tilde (~) specifies the word "Hello" as the wildcard character does not follow it.

How To Use Wildcard In Excel?

Example #1 – Usage Of Excel Wildcard Character Asterisk (*)

As we discussed, an asterisk is used to match any number of characters in the sentence.

  1. Look at the below data.

    Step 1 - Data

  2. In the above data, we have names. With these names, we have many names with the common word "Abhishek." So, using a wildcard asterisk, we can count all the "Abhishek" here. Open the COUNTIF function and select the range.

    Step 2 - Open COUNTIF Function

  3. In the criteria argument, mention the criteria as "Abhishek*."

    Step 3 - Mention the criteria

  4. That is all. It will count all the words "Abhishek" in them.

    step 4 - Count all words

Example #2 – Partial Lookup Value In VLOOKUP

VLOOKUP requires an exact lookup_value to be matched to fetch the data. It is the traditional slogan, but we can still fetch the data using particle lookup_value. So, for example, if the lookup value is "VIVO" and in the main table, if it is "VIVO Mobile," we can still match using wildcard characters. We will see one of the examples now. Below is the example data.

Wildcard in Excel Example 2

We have a lookup table in column A. In column C, we have lookup values. These lookup values are not the same as the lookup table values. So, we will see how to apply VLOOKUP using wildcards.

First, open the VLOOKUP function in the D1 cell.

Wildcard in Excel Example 2-1

The first argument is the lookup value. One of the problems with the lookup value here is we do not have an exact match, so we need to enclose this lookup value with an asterisk before and after the lookup value.

Wildcard in Excel Example 2-2

We have applied two asterisk characters, "*" &C2&"*." Here, an asterisk indicates that we should match anything between the wildcard, and it should return the related result.

Wildcard in Excel Example 2-3

Even though we had just "Infosys," the asterisk character matched the value in the lookup table and returned the exact result as "Infosys Ltd."

Similarly, in cell D6, we got the error value as #VALUE! Because there is no word “Mintra” in the lookup table.

Example #3 – Usage Of Excel Wildcard Character Question Mark (?)

As we discussed, the question mark can match a single character in the specified slot. For example, look at the below data.

Question Mark (?) Example 3

In the above data, our ideal value should be "Wallstreet Mojo," but we have several special characters in between. So, we will use the question mark to replace all of those.

Select the data and press "Ctrl + H."

Question Mark (?) Example 3-1

In the "Find what" box, type "Wallstreet?Mojo." In the "Replace with" box, type "Wallstreet Mojo."

Question Mark (?) Example 3-2

Click on Replace All. We will get the below result.

Question Mark (?) Example 3-3

Nice, isn’t it??

The trick is done here by Excel wildcard character question mark (?). First, let us take a look at what we have mentioned.

Find What: “Wallstreet?Mojo”

Replace With: “Wallstreet Mojo”

So, after the word Wallstreet any character that the space character should replace. So, all the special characters are replaced by a space character, and we will have a proper value of "Wallstreet Mojo."

Like this, by using wildcard characters, we can match partial data and get the job done.

Excel Formulas That Support Excel Wildcards

From this article, it is evident that wildcard is useful to find results effectively. But, wildcard cannot be used with all formulas in Excel.

1. Why do we use wildcards in Excel?

When searching and replacing content or using conditional formatting rules, utilize wildcard characters as comparison criteria for text filters that use the "Format cells that contain specific text" criteria.

For example, consider the below table showing a list of names. Now, using COUNTIF function with wildcard, let us find the number of times, the name ‘Darwin’ is used.
Wildcard Faq 1

The steps are:
 
Step 1: First, insert the formula with wildcard asterisk *. So, the formula is =COUNTIF(A1:A10,”Darwin*”)
 
Step 2: Press Enter.
 
We can see the result as shown in the below image.

Wildcard Faq 1 - step 2
Likewise, we can use wildcard in Excel.

2. How do I use wildcards in Excel with numbers?

In Excel, you can use the "*" and "?" wildcard characters to perform partial matches in different lookup formulas. However, it's important to note that if you use wildcards with a number, the numeric value will be converted to a text value.

3. Can you use wildcards in Excel filter function?

If you want to filter at the beginning of each cell, you can use the LEFT function. To determine the number of characters you need to isolate, you can use the LEN function in Excel to calculate the total number of characters in the search field. This function will help you to specify the number of characters you want to focus on.

Some of the functions which support wildcard in Excel are:

  • AVERAGEIF and AVERAGEIFS
  • COUNTIF and COUNTIFS
  • MATCH
  • MAXIFS
  • MINIFS
  • SEARCH
  • SUMIF, SUMIFS
  • VLOOKUP, HLOOKUP, XLOOKUP support wildcard in Excel.

Important Things To Note

  • Wildcards are special Excel characters used to find effective results.
  • We can use 3 types of wildcards (asterisk, question mark and tilde) in Excel.
  • Remember, not all functions support wildcards in Excel.

Frequently Asked Questions