WILDCARD

WILDCARDS IN EXCEL

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 tideused to distiguish 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

EXCEL WILDCARD

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

Now, What if you want those that relates 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 inbetween.

=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 a formula then you have to add an ampersand & thus “<=”&DATE(2014,5,30)

►You can replace SUMIF with COUNTIF, or AVERAGEIFS in Forumal 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?

EXCEL WILDCARD

►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

Wildcard

Counting non-Blank Fields

Counting blank fields is easy just 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, Octomber  and December dates to read “LAST QUATER”

FIND & 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

Related Articles

SUMPRODUCT WITH WILD CARDS

Leave a Comment:

Your email address will not be published. Required fields are marked *