How to Use Wildcard Characters in Excel

Sometimes working with data is not that easy, sometimes we face complex problems.

Let say, you want to search for the text “Delhi” from the data. But, in that data, you have word “New Delhi” not of “Delhi”.

Now, in this situation, you can’t match or search.

Here, you need a way which allows you to use only available value or partial value (Delhi) to match or search for the actual value (New Delhi).

Excel Wildcard Characters are meant for this.

These wildcard characters are all about searching/looking up for a text with a partial match. In Excel, the biggest benefit of these characters is with lookup and text functions.

It gives you more flexibility when the value is unknown or partially available.

So today, in this post, I’d like to tell you what are wildcard characters, how to use them, their types and examples to use them with different functions.

So let’s get started.

What's a Wildcard Character

Wildcard character is a special kind of a character that represents one or more other characters.

It's used in regular expressions, by replacing them with unknown characters.

In simple words, when you are not sure about an exact character to use, you can use a wildcard character in that place.

Types of Excel Wildcard Characters

In Excel, you have 3 different wildcard characters Asterisk, Question Mark, and Tilde. And each of these has their own significance and usage.

Ahead, we'll discuss each of these characters in detail so that you would be able to use them in different situations.

1. Asterisk(*)

An asterisk is one of the most popular wildcard characters.

It can find any number of characters (in sequence) from a text. For example, if you use Pi* it will give you words like Pivot, Picture, Picnic etc.

If you notice, all these words have Pi in starting and then different characters after that. Let's understand its working with an example.

Below you have a data where you have names of cities and their classification in a single cell.

city data to use with asterisk wildcard characters in excel1

Now from here, you need to count the number of cities in each classification. and for this, you can use an asterisk with COUNTIF.

=COUNTIF(City,Classification&"*")

formula to use with asterisk wildcard characters in excel

In this formula, you have combined classification text from the cell with an asterisk. So, the formula only matches the classification text in the cell and the asterisk works as rest of the characters.

And in the end, you get the count of cities according to classification.

2. Question Mark

With a question mark, you can replace one single character from a text. In simple words, it helps you get more specific, still using a partial match.

For example, if you use c?amps it will return champs and chimps from the data.

3. Tilde (~)

The real use of a tilde is to nullify the effect of a wildcard character. For example, let’s say you want to find the exact phrase pivot*.

If you use pivot* as a string, it would give you any word that has champs at the beginning (such as pivot table, pivot chart).

To specifically the string pivot*, you need to use ~. So you string would be pivot~*. Here, ~ ensures that Excel reads the following character as is, and not as a wildcard.

Wildcard Characters with Excel Functions

You can extend this list with all the functions which you use for matching a value, for lookups or find a text.

Ahead, I'll share with you some of examples with functions + find and replace options + conditional formatting + with numbers.

1. With SUMIF

Below you have data where you have invoice numbers and their amount. And, we need to sum amounts where invoice numbers have “Product-A” in starting.

data to use wildcard characters with sumif excel

For doing this we can use SUMIF with wildcard characters. And the formula will be:

=SUMIF(F2:F11,”Product-A*”,G2:G11)

formula to use wildcard characters with sumif excel

In the above example, we have used an asterisk with the criteria at the end. As we have learned an asterisk can find a partial text with any number of sequence characters.

And, here we have used it with “Product-A”. So, it will match the values where “Product-A” is at the beginning of the value.

Let’s say you have a list of students with full name and their marks.

Now, you want to use VLOOKUP to get marks in another list in which you only have their first names.

And for this the formula will be:

=VLOOKUP(first_name&”*”,marks_table,2,0)

In above example, you have used vlookup with an asterisk to get marks by only using the first name.

3. For Find and Replace

Using wildcard characters with find and replace option can do wonders in your data.

using excel wildcard characters with find replace options

In above example, you have a word “ExcelChamps” which has different characters between it. And, you want to replace those characters with a simple space.

You can even do this by using replace option but in that case, you have to replace each character one by one.

But, you can use a question mark (?) to replace all the other characters with space. Find “Excel?Champs” & replace it with “Excel Champs” to remove all unwanted characters.

4. In Conditional Formatting

You can also use these wildcard characters with conditional formatting.

Let’s say, from a list, you want to highlight the name of the cities which are starting from letter A.

  • First of all, select the first entry from the list and then go to Home → Styles → Conditional Formatting.
  • Now, click on “New Rule” option, select use a formula to determine which cells to format”.
  • In formula input box, enter =IF(COUNTIF(E2,”A*”),TRUE,FALSE).
  • Set the desired formatting, you want to use to highlight.
  • Now, I have used conditional formatting in the first cell of the list & I have to apply it to the entire list.
  • Select the first cell on which you have applied your formatting rule.
  • Go to Home → Clipboard → format painter.
  • Select entire list and it will highlight all the names starting with letter A.

5. Filter values

Wildcard characters work like magic in filters.

Let’s say you have a list of cities and you want to filter city names starting from A, B, C or any other letter.You can use A* or B* or C* for that.

use excel wildcard characters with filters

This will give you the exact name of cities which are starting from that letter.

And, if you want to search any character which is ending with a specific letter you can use it before that letter (*A, *B,*C, etc).

6. With Numbers

You can use these wildcards with numbers but you have to convert those numbers into text.

use wildcard characters in excel when you have only numbers

In above example, you have used match function and text function to perform a partial match.

Text function returns an array by converting entire range into the text and after that match function, lookup for a value which is starting from 98. 

Sample File

Conclusion

With wildcard characters, you can enhance the power of functions when your data is not in a proper format.

These characters help you search or match a value using a partial match which gives you a power deal with irregular text values.

And, the best part is you have three different characters to use.

Have you ever used these wildcard characters before?

Share your views with me in the comment section, I'd love to hear from you. And, don't forget to share this tip with your friends.

You Must Read These Excel Tutorials

  • Steve

    Great examples… Really like the Conditional Formatting one!

    • Puneet Gogia

      Thanks Steve for your words.

  • A1 contains the word ‘Sonu Monu’ (without quotes) and in B1 I’ve this formula, =If(A1> “*Monu”, “yes”, “No”). I expect this formula to result in ‘Yes’, but it is resulting in ‘No’. Why?
    Doesn’t the * wild character imply that there could be any characters before the word ‘Monu’?

    • Puneet Gogia

      You are using wrong operator. It should be = or . Greater than and lower than doesn’t work with text.

      • Thank you very much indeed!!

        • Puneet Gogia

          You’re Welcome.

      • I used this formula in the above scenario: =If(A1=”*Monu”,”Y”, “N”). The result was ‘N’, even when I expected ‘Y’. Why? What goes here buddy.!

  • James Wheeler Hammontree

    Thank you for sharing all of this. Your formula for the conditional formatting example did not work for me with the IF function. Took me some head scratching and beard stroking, but I got it to work using just the COUNTIF portion of the formula.

    • That’s great. Keep it up.

  • Carlos Mario Castaño

    Puneet: I think that in

    ” …If you use pivot* as a string, it would give you any word that has champs at the beginning (such as pivot table, pivot chart). ”

    you must change “champs” by “pivot.”
    Carlos