How to save time using wildcards in Excel

In the first of a new Kinase series of ‘How To’ articles, Jack Gamwell takes us through a Wildcard masterclass

There are only 3 wildcards within Excel and even though they’re few in number, they certainly make up for it in usefulness. Within this guide, we will explain the basic principles of how each wildcard works, progressing onto where they can really save you time in your formulas.

What are the three wildcards?

The Asterix (*), the question mark (?) and the tilde (~) are the 3 symbols used within excel to makeup our wildcards. All 3 of these focus on helping you deal with text rather than numbers, and at a fundamental level can increase efficiency filtering said text.

Asterix in action

Using the Asterix as our main example, it can filter a long list of text data based off of a specific condition. Take the data set below:

If you wanted to filter this data to find cities starting with the letter ‘A’, without the wildcard you would run into an issue, with the filter view providing all cities containing the letter ‘A’:

Here’s where the Asterix comes into play, by placing it after the ‘A’ – shown as ‘A*’, the filter will only show fields that start with an ‘A’:

You can also use this to filter for fields ending in a specific way by putting the Asterix in front of the text (*A). This wildcard is not limited by one letter - a string of text before, after or in between the Asterix/s can be as long as required:

·        Lon* = Fields starting in ‘Lon’

·        *don = Fields ending in ‘don’

·        *London* = Fields containing ‘London’

Question mark in action

The question mark (?), can be used in a similar way to the asterix. However, this doesn’t search based upon what character you’re looking for, rather the number of characters in the field. For example, using our previous data set if we wanted to see how many cities are 6 characters in length we would type ‘??????’ into the filter to get the below results:

Finally, the tilde

Finally, there is the tilde (~), its purpose is for when you want to search for something containing an ‘*’ or ‘?’. For example, if we wanted to find the exact phrase ‘New*’, excel would filter for any field starting with ‘New’ followed by any potential characters after. To combat this, we would type it out as ‘New~*’. The tilde ensures that Excel reads the ‘*’ as a character, and not as a wildcard.

Using wildcards in formulae

Now the foundations are laid for how wildcards operate, they can be used very effectively within formulas to save you time. For example, using the data set below:

If we wanted to find out how many sales blue t-shirts obtained, regardless of if they are long/short sleeve etc. you can incorporate wildcards into a formula to get your result:

=SUMIFS(E2:E21,C2:C21,"*T-Shirts*",D2:D21,"*Blue*")

If you only remember one thing about Excel wildcards…

These handy symbols are best applied when dealing with large data sets involving text, where manual calculation proves challenging or pivot tables too inflexible.