Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

Wildcard Characters

Sometimes while looking for a match or searching for a value in Excel, we don’t have the exact value to use. For example, we want to search for “Delhi” and in the actual data, we have value “New Delhi”.

Here both have “Delhi” in them but don’t have an exact match, Now, in this situation, we can’t match or search.

So, we need a way which can allow us to use only available value or partial value (Delhi) to match or search for the actual value (New Delhi).

Wildcard Characters are meant for this. These characters are all about searching a text with a partial match.

In Excel, the biggest benefit of a wildcard is with lookup functions. It gives more flexibility to all those functions.

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

So let's get started.

Wildcard Characters: Types

In Excel, we have 3 wildcard characters. Each of these has a its own significant and can help you to take up any value.

1. Asterisk (*)

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.

2. Question Mark (?)

By using question mark you can find text in which ? will represent a letter. For Example: If you use P?inter it will give you words like Printer & Painter.

3. Tilde (~)

By using tilde you can nullify the impact of above two wildcard characters. For Example: If you want to search for a text Pivot*, Find & Replace option will take Pivot as a text & * as a wildcard. But if you use Pivot*~ it will exactly find a text with Pivot*.

Wildcard Characters: Examples

We can easily use all the three wildcard characters with all the top most functions. Functions like VLOOKUP, HLOOKUP, SUMIF, SUMIFS, COUNTIF, COUNTIFS, SEARCH, FIND, and INDEX MATCH.

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

Ahead, we will learn to use wildcards with functions + find and replace options + conditional formatting + with numbers.

Using SUMIF with Wildcards

Below we have data where we have some invoice numbers with the amount. And, we need to sum amounts where invoice numbers have “Product-A” in starting.

SUMIF with Wildcard Characters In Excel

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

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

Asterisk of Wildcard Characters In Excel With Sumif

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.

VLOOKUP with Wildcard Characters

Let’s say we have a list of students with full name and their marks. Now, we want to use VLOOKUP to get marks in another list in which we only have their first names.

using vlookup with wildcard characters

And for this the formula will be:

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

enter vlookup with wildcard characters

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

Wildcards Characters in Find & Replace

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

Let me show you an example.

Wildcard-Characters-In-Excel-10

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

I can even do this by using replace option but in that case, I 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.

Wildcard Characters for Conditional Formatting

You can also use these wildcard characters with conditional formatting.

Wildcard-Characters-In-Excel-7

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

  • Select the first entry from the list.
  • Go to Home → Styles → Conditional Formatting.
  • Click on “New Rule” option.
  • Select  “use a formula to determine which cells to format”.
  • In formula input box, enter the formula. =IF(COUNTIF(E2,”A*”),TRUE,FALSE).
  • Set the desired formatting, you want to use to highlight.

Wildcard-Characters-In-Excel-8

  • 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 & it will highlight all the names starting with letter A.

Wildcard-Characters-In-Excel-9

WildCard Characters In 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. This will give me the exact name of cities which are starting from that letter.

Wildcard-Characters-In-Excel-7

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.)

Apply WildCard Characters In Excel If You Only Have Numbers

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

Wildcard-Characters-In-Excel-10

In above example, I have used match function & text function to perform a partial match.

Text function will convert entire range into the text & in match function, you can use lookup values with an asterisk to perform a partial match.

Sample File

Last Word

This is not the end of the story.

You can use wildcards characters in excel with different functions.

Using wildcard characters in Excel let you manipulate your data in a smart way.

I hope these wildcard characters will help you.

Over To You

Do you have any other idea to use wildcard characters?

Share with me in the comment box.

More Tips



  • 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.