A Wildcard is a symbol used to replace or represent one or more characters. Excel wildcards are either an asterisk (*) which represents one or more characters, a question mark (?) which represents a single character or a tide (~) used to distinguish between normal characters and a wildcard. A tide is rarely used.

Examples:

â–ºM* will return words STARTING with M e.g. Mark, Miker, Me, Malicious…etc

â–º*M will return words ENDING with M e.g Liam, Brim, Trim, Cream….etc

â–º*M* will return words that CONTAINS, whether at the start, middle or end e.g Come, Milk, Seem, Gammer..etc

►M*K  will return words that STARTS with M followed by Any Number of characters   and ENDS With K e.g. Milk, Multitrack, Maverick

►M?K will return words that STARTS with M followed by a Single character and ENDS With K e.g Mik, Mok, Mak, Muk

►M????K will return words that STARTS with M followed by ANY 4 characters  and ENDS With K e.g Medick, Muzhik, Mamluk, Moujik

►M??K* will return words that STARTS with M  followed by ANY 2 characters then a K and ENDS with Any Number of characters

NB:

â–ºThe position(s) of the wildcard in a text determines whether you want texts that STARTS, ENDS or CONTAINS a certain character(s)

Where Are Wildcards used?

Creating a summary using SUMIFS, AVERAGEIFS or COUNTIF

For example, if you have a list of cost codes like below and you want to get total cost for all cost codes that STARTS with a K and ENDS with a J

wildcard in excel

=SUMIFS(B2:B24,A2:A24,"K*J")= 364,160

Now, What if you want those that relate to the Month of May Only? Can we use Wildcards on dates?

Well, Wildcards do not work on dates, you have to define the Start Date and the End date and excel will calculate what falls in-between.

=SUMIFS(B2:B24,A2:A24,"K*J",C2:C24,">="&DATE(2014,5,1),C2:C24,"<="&DATE(2014,5,30))=121,000

How does this work?

â–ºA2:A24,"K*J" determines which cost codes to add up
â–ºC2:C24,">="&DATE(2014,5,1) defines the START of the Period 
►C2:C24,"<="&DATE(2014,5,30)   defines the END of the Period

►Sumif then adds cost  that meets all 3 criteria.

NB:

â–ºIf you are using an operator inside a formula you have to enclose it with quotation marks “”   as it is done with Texts. if the operator is to be used together with formula then you have to add an ampersand & thus “<=”&DATE(2014,5,30)

►You can replace SUMIF with COUNTIF, or AVERAGEIFS in Formula above

=AVERAGEIFS(B2:B24,A2:A24,"K*J")= 30,347----Average of cost codes
=COUNTIF(A2:A24,"K*J") = 12 -------Number of cost codes that starts with K and ends with J

Filter Data with WildCard

Let’s say you are in HR and you would like to filter only Officer Positions from below data. How would go about it?

wildcards HR

►Go to Data –> Sort and Filter

â–ºClick filter Icon and on the text field type *Officer* as you want all Positions containing the name officer

â–ºClick OK to display filtered positions

Counting non-Blank Fields

Counting blank fields is easy just to use =COUNTBLANK(range) but sometimes there are hidden characters and you want to calculate non-blank fields.

This will require you to use a wildcard

=COUNTIF(range,"?*")

Find and Replace

Wildcards can fasten the cleaning of data, for example, if you have below dates and you want all November, October  and December dates to read “LAST QUARTER”

wildcard Find and Replace

â–ºHighlight the Date Column

►Use the keyboard shortcut – Control + H to invoke Find & Replace

Find what *-1?-2014

Replace with LAST QUATER

â–º Replace All

Download Worksheet WILD CARDS

Related Articles

SUMPRODUCT WITH WILD CARDS

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.