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.
Calculate age based on birthday (get the age by a given date. There are dozens of usually used formulas in the Formula Helper.
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.
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.
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.
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:
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 60 days.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Click Enterprise > 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 click 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 KT, * indicates any strings before or after KT. See screenshot:
(2) Click Filter button, now the data is filtered.
Filter by multiple substrings
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. See screenshot:
(2) Now you can set your criteria as below screenshot shown:
(3) 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.
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 60 days.
With Super Filter utility, you also can do these operations:
|In Excel general sort, you only can sort data by values/cell color/font color/cell icon, but with Kutools for Excel's Advanced Sort, you can sort data by last name, text length, day or other criteria.|