Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 good idea3


Calculate age based on birthday (get the age by a given date. There are dozens of usually used formulas in the Formula Helper.

doc calculate age

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:

doc filter by substring 1

2. Click Data > Advanced. And then in the popping dialog, select the data range and the criteria range. See screenshot:

doc filter by substring 2

3. Click OK. Now you can see all data with KT is filtered.

doc filter by substring 3

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:
doc filter by substring 4

2. Then type the substring you want to filter by into the text box next to contains. See screenshot:

doc filter by substring 5

3. Click OK. Now you can see the data is filtered correctly.

doc filter by substring 6


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:
doc filter by substring 7

1. Type the criteria into cells as below screenshot shown:

doc filter by substring 8

2. Click Data > Advanced, then in the popping dialog, select the data range and the criteria range. See screenshot:

doc filter by substring 9

3. Click OK. And the data which includes melon or mango is filtered.

doc filter by substring 10

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:

doc filter by substring 11


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 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.

doc kte filter by substring 1

2. Then click to select a data range you need to filter in.

doc kte filter by substring 2

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:

doc filter by kte substring 8
doc kte filter by substring 3

(2) Click Filter button, now the data is filtered.

doc kte filter by substring 4

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. See screenshot:

doc kte filter by substring 5

(2) Now you can set your criteria as below screenshot shown:

doc kte filter by substring 6

(3) Click Filter button, and then the data which end with melon or begin with mango is filtered.

doc kte filter by substring 7

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:

Filter data by asterisk or other special symbol in Excel

Filter data by multiple criteria or wildcard

Advanced Sort 

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.
doc advanced sort last name

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.