Excel Custom AutoFilter with Wildcards

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

Excel’s Custom AutoFilter can perform a myriad of tasks that are often overlooked.

Just last week we had a question from Alf:

“I have a list of 7000+ words and want to search it with variables such as find all words where the third character is "u", or where the third character is "u" and the fifth character is "d".”

The solution is in Excel’s custom AutoFilter and using wildcards in the criteria.

Let’s look at an example. Here’s our data (it’s a little different to Alf’s):

Let’s say I want to filter my list to display only SKU’s where the 2^nd character is B and the 4^th is 3. It’s easy with a custom AutoFilter.

Turn on Filters

First you need to turn on the AutoFilters. To do this:

Give your columns header labels (if you haven’t already) then you can either:

1. Format your data in an Excel Table and the filter buttons will be inserted automatically. To do this select a cell in your data range > CTRL+T and make sure the ‘my table has headers’ box is checked:
 
2. Or, select a cell in the header row > Data tab > Filter:
 

Now you should have the drop down arrows beside your column labels (circled in orange below) and you’re good to go:

Custom AutoFilter

To recap; I want to filter my list to display only SKU’s where the 2nd character is B and the 4th is the number 3.

Click on the drop down arrow in column A > Text Filters > Contains:

In the dialog box enter ?B?3:

Now my list (see below) only displays the SKU’s that match my filter criteria:

[Edit]: Note, if there is a possibility that your data could have the filter criteria, in my case ?B?3, appear more than once in the text, then instead of 'Contains' use 'Begins with' ?B?3, or 'Equals' ?B?3*

AutoFilter Wildcards

There are three wildcards you can use with AutoFilter:

1. ? The question mark (?) is a place holder for any character. Taking my example above I didn’t care what the first and third characters were, so by placing a ? in the filter criteria I was able to tell Excel to filter my list where the second and fourth characters met my criteria.
2. * You can also use the asterisk (*) as a wildcard to represent a series of characters but this is the same as choosing Text Filters > Contains.
3. ~ Lastly the tilde (~) allows you to search for wildcard characters. When you prefix a wildcard with the tilde you’re telling Excel that it should look for the character, not use it as a wild card. See example below: Here I want to search for SKU’s where the second character is a ?. So my custom AutoFilter criteria would is ?~?. The first ? is the placeholder and the ~? together tell Excel to look for SKU’s where the question mark is the second character:
 

Note:

Wildcards can only be used to filter text stings. They don’t work with numbers.

Thanks

I’d like to thank Catalin for reminding me of this feature in his reply to Alf who asked how it could be done.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.