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

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
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.