how to use wildcard character in excelDo you use wildcard characters in excel?

They are life savers.

Wildcard characters are all about searching a text with a partial match.

Let’s say you have a list of names & you want to search for the names who are starting with “T” and ending with “I”.

You can use a question mark (?) to search for the names with your criteria.

In this post, you will learn how to use wildcard characters in excel. And, you will also learn some real life examples to use wildcard characters.

Table of Content

  1. Types
  2. Using with SUMIF
  3. With VLOOKUP
  4. In Find & Replace
  5. For Conditional Formatting
  6. In Filter Values
  7. With Numbers

Types

We have three wildcard characters.

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

Now, let’s come to some examples to understand how you can use wildcard characters in excel.

Using SUMIF with Wildcard Characters

If look at the below data in which I have some invoice numbers with the amount.

And, I want to sum amounts where invoice numbers have “Product-A” in starting.

using wildcard characters in excel with sumif

For doing this I will use sumif function with wildcard characters.

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

Asterisk of Wildcard Characters In Excel With Sumif

In above formula, I have used an asterisk (*) with criteria. Now, sumif will sum all the amounts where invoice no. have “Product-A” in the starting.

VlookUp with Wildcard Characters

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

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

Use VlookUp with Wildcard Characters In Excel

Formula will be =VLOOKUP(Name&”*”,$D$2:$E$9,2,0).

Wildcard-Characters-In-Excel-6

In above example, I have used vlookup with an asterisk to get marks by only using the first name. It will return marks where get the first name in the cell.

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.

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