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 tide ~ used to distiguish between normal characters and a wildcard. A tide is rarely used.
►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
►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
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.
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.
►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?
►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 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
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”
►Highlight the Date Column
►Use the keyboard shortcut – Control + H to invoke Find & Replace
Find what *-1?-2014 Replace with LAST QUATER
► Replace All