How to filter data based on substring in Excel?
some time, you may want to filter out the data with some special substring from a large amount of data, and how can you do? Now I will tell you how to filter them by using wildcards in Excel.
Filter data based on one string
Filter data based on multiple strings
Quickly filter data based on one or multiple strings with Super Filter 
Filter data based on one string
Be honest, with the Excel’s Filter function is not easy and quick enough because there are so many criteria setting needed to make in particular when you filter based on multiple strings. If you want to save time and solve task quickly, you can directly go to Quickly filter data based on one or multiple strings with Super Filter. But if you still want to know more about Filter function, you can read the below steps one by one.
Actually, you can filter data based on one substring by two methods.
Advanced Filter
1. Type asterisk symbols at the two sides of the substring you want to filter based on, and remember to type the column header of which column you want to filter based on above the substring as well. See screenshot:
2. Click Data > Advanced. And then in the popping dialog, select the data range and the criteria range. See screenshot:
3. Click OK. Now you can see all data with KT is filtered.
Custom AutoFilter
You also can filter data based on a specific substring with Custom AutoFilter in Excel.
1. Select the data range, and click Data > Filter. Then click at the arrow at right side of column header of which column you want to filter on, and click Text Filters or Number Filters > Contains. See screenshot:
2. Then type the substring you want to filter by into the text box next to contains. See screenshot:
3. Click OK. Now you can see the data is filtered correctly.
Filter data based on multiple strings
If you want to filter data based on multiple strings, such as you want to filter data which contains melon or mango from below data range, you can do as follows:
1. Type the criteria into cells as below screenshot shown:
2. Click Data > Advanced, then in the popping dialog, select the data range and the criteria range. See screenshot:
3. Click OK. And the data which includes melon or mango is filtered.
Note: Also you can use Custom AutoFilter to filter the data based on multiple substrings. Just please remember to check Or in the Custom AutoFilter dialog. See screenshot:
Quickly filter data based on one or multiple strings with Super Filter
With Excel’s Filter function to filter data is a little complex, but if you use the advanced Super Filter of Kutools for Excel will be easier. For better to follow below steps, you can free download Super Filter and have a try, and it has free trail for 30 days.
1. Click Kutools Plus > Super Filter to enable the Super Filter pane. See screenshot:
Tip: You can resize and move the Super Filter pane by dragging its border.
2. Then check Specified checkbox, and click the select range button to select a data range you need to filter in.
3. Now you can set the substring you need to filter by in the pane.
Filter by one substring
(1) In the Super Filter pane, do these operatios:
Move mouse right to the AND or OR to display a underline, next click at the underline to show the textboxes;
Then specify the column you need to filter on in the first textbox, then select the Text in the second textbox, click Contains from the third textbox;
And type the substring you need in the last textbox, here I want to filter out the data contains KTE. See screenshot:
(2) Click Filter button, now the data is filtered.
Filter by multiple substrings
OR relationship
If you want to filter data by multiple substrings in OR relationship which means that you want to filter data out when it meets one or more of the substrings you list.
(1) After you selecting data range, you need to select OR both in Relationship and Relationship in Group.
(2) Now you can set your criteria then click Filter button, and then the data which end with melon or begin with mango is filtered.
If you want to know more about the filter criteria and wildcard using, you can read this article How to filter data by multiple criteria and wildcard in Excel.
Super Filter
The Super Filter function also can help you filter data by date, quarter and so on. And Super Filter is only one of hundreds of function in Kutools for Excel, you can free download kutools for Excel and try it out for 30 days.
With Super Filter utility, you also can do these operations:
Filter data by asterisk or other special symbol in Excel
Filter data by multiple criteria or wildcard
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
