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 2nd character is B and the 4th 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:
- 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:
- 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:
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*
There are three wildcards you can use with AutoFilter:
- ? 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.
- * 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.
- ~ 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:
Wildcards can only be used to filter text stings. They don’t work with numbers.
I’d like to thank Catalin for reminding me of this feature in his reply to Alf who asked how it could be done.
Nice Tip 🙂
Kindly provide custome filter workbook .
There is no workbook, you can do that with any data, the article is providing all the steps you need to apply a filter to your data set.
Its really interesting, Thank you, Please post some thing about pivot tables too.
You’re welcome, Suchitra. Here is a link to our PivotTable tutorials:
I think we may input the search string with ? or * directly into the “Search box” that is right below the “text filter”. 🙂
Sometimes things are right in front of you and you still don’t see them! Thanks, MF. Great tip.
Note: you have to enter ?B?3* in the search box as opposed to just ?B?3
Another thing that is right in front of us and being ignored is:
Clear Filter from xxx.
True. I like to have the clear filter icon in my QAT.
I think you need to filter for ” Equals… ?B?3* ” or ” Begins With… ?B?3 ”
filtering for ” Contains…” will also find that text in other positions in the text
also, I use ctrl-L for creating a table, because it’s so similar to ctrl-shift-L for auto-filtering
Good point, I guess if my data had more than 3 letters I’d need to consider more scenarios.
I get an error with CTRL+SHIFT+L… must be missing something.
can’t think why, ctrl-shift-L is even highlighted as a shortcut in your Option 2 above
Ha, I have an add-in that uses that shortcut!