Archer Dynamics

The Problem

Ever tried to use Excel’s filter and looked for records with specific prefixes, suffixes, strings, or format? It can be maddening, if you have to manually check the boxes for all that meet the criteria, because dirty data can make records show up in the filter more than once (I’m looking at you trailing white spaces! I see you!).

Wildcards come to the rescue

All you really have to remember are two simple yet powerful characters. They are both your friends.

* = any number of characters
? = exactly one character

So how does this work? Let’s look at one very simple example. Suppose we have a list of power tools our store has for sale, but each has different spelling or case. We just want to see what Milwaukee tools we have:

From this table you can see there are quite a few, but their brand spelling is all over the place. So, you turn it into a table, click on the filter and then manually check the boxes for only those which have some version of Milwaukee in them, hoping that they all fall under M and that there is no “The Milwaukee Tool Company”, hiding down the list somewhere.

Or, you could use a wildcard! How?

*milwaukee*

If you type this in the Search file of the filter, you will get any record which contains the word milwaukee, regardless of upper or lower case.

But we don’t have to stop here! Suppose you have a list of invoices. Thousands upon thousands of rows and their naming scheme has the invoice date built in, something like

INV-20240526-0032

So you know that your invoice starts with INV-, then has a full year in YYYYMMDD format and then a 1 to 4 digit suffix.
Suppose you want to filter by only invoices which were in the month of July 2025. Simple:

INV-??2507??-*

It will find all records, which have 2507 in the YYMM position and it will pull all records regardless of whether the suffix behind the dash is 1, 2, 3 or 4 digits long.
If you knew that you wanted only invoices with 2-digit suffixes, you’d write it like this:

INV-????07??-??

Pretty neat. I look at Excel’s wildcards as RegEx’s much friendlier brother. Sometimes the 80% solution that takes 5 seconds is better than the perfect elaborate solution that took 5 hours.

Need Help?

Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.

Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for newsletter updates: goarcherdynamics.com