Mahbubur Rahman
Advance Filter in Excel is one of the coolest options in Excel. Advance Filter can do many complex filtering that AutoFilter can not do. In this article, we will see How to Use Advanced Filter If Criteria Range Contains Text in Excel. We will use a sample dataset for convenience which contains Date, Name, Item, and Sales. Using this dataset we will extract data for several complex criteria.
Download Practice Workbook
5 Ways of Using Excel Advanced Filter When Criteria Range Contains Text
Throughout this post, we will get to know about AND, OR, and Wildcard characters for filtering data and the use of wildcard characters for text criteria.
Method 1: Advanced Filter for Cell That Contains Unique Text Values
As you can see, in the dataset we have some duplicate values. We will see how to remove these duplicates and generate a unique dataset.
Steps:
- First, click on the Data tab and choose Advance Filter or press ALT+A+Q
- Now, a dialogue box will pop up and we will select Copy to another location, then our data range $B$4:$E$14, then in Copy to the section, we will select a cell where we want to copy our unique values, at last click on Unique record only and click OK.
As a result, our dataset will look like the following image now.
You can see, there are no duplicate values, only unique text records.
Read More: How to Use Advanced Filter for Unique Records Only in Excel
Method 2: Advanced Filter for Cells Whose Values are Exactly Equal to Text Criteria
Suppose, we want to extract data for a name that contains Brad and has a Sales value greater than $50.
Steps:
- First, we will create a dataset similar to the original column. Like the image below.
- Now, a dialogue box will pop up and we will do as the following image.
Here, first of all, we selected Copy to another location, otherwise, the dataset will be filtered in its exact location, then selected the List Range including Header, after that, we selected the Criteria range, as earlier we created a similar subset for the data and finally selected the area where we wanted our records and clicked OK.
As you can see, the Advanced Filter extracted data exactly what we wanted.
Read More: How to Apply the Advanced Filter to Copy to Another Location in Excel
Method 3: Advanced Filter for Text Values with Wildcard Characters
We can use three wildcard characters asterisk (*), question mark (?), and tilde (~) while constructing criteria, let’s see how to use these.
3.1: Filter Cells That Begin with the Text
Suppose, we want to extract names that begin with Leo.
Steps:
- First, we will create a dataset similar to the original column. Like the image below.
You can see, we put an asterisk sign after Leo, like this Leo*.
- So, a dialogue box will pop up and fill the criteria as shown in the image below.
Here, first of all, we selected Copy to another location, otherwise, the dataset will be filtered in its exact location, then selected the List Range including header, after that, we selected the Criteria range, as earlier we created a similar subset for the data and finally selected the area where we wanted our records and clicked OK.
We have a Name value Johnson Leo, but it doesn’t start with Leo, that’s why we didn’t get it in our extracted data.
3.2: Filter Cells Using Question Mark
Now, we want all the items that start with Sh and then have another letter after that and the following letters rt. Simplifying Shirt and Shorts.
Steps:
- First, we will create a dataset similar to the original column. Like the image below.
After that, follow Method 3.1 and the result will be as follows.
Read More: Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]
Similar Readings
- How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)
- Dynamic Advanced Filter Excel (VBA & Macro)
- Excel VBA Examples: Use Advanced Filter with Criteria (6 Criteria)
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Excel Advanced Filter Not Working (2 Reasons & Solutions)
Method 4: Advanced Filter for Text Values with AND Rule
When using AND logic we have to keep our wanted data side by side. Suppose, we want data where names have Leo in it and the item is pant. So, let’s see how we will do it.
Steps:
- First, we will create a dataset similar to the original column. Like the image below.
- Now, press ALT+A+Q and a dialogue box will up. From there we will do as the following image shows.
- So, how to fill the List range, Criteria range, you already know. Yeah, the similar task we’ve done in Method 2.
Finally, our dataset will look like the following image.
Related Content: Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
Method 5: Advanced Filter for Text Criteria with OR Rule
We will now see the use of OR logic. Now, we want to know Brad in Name or Item as Shirt, Shorts or Trouser. Any of the data in the row or column means valuable for us. So, let’s get into this.
Steps:
- First, we will create a dataset similar to the original column. Like the image below.
- Now, you know, how to use Advanced Filter from now on. You can get help from Method 2. Our final result will look like the following image.
Read More: How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
These are 5 different methods for using Excel Advanced Filter with Criteria Range Contains Text. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback
Related Articles
- Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)
- Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
- VBA to Copy Data to Another Sheet with Advanced Filter in Excel
- Apply Advanced Filter Based on Multiple Criteria in One Column in Excel
- Advanced Filter with Criteria Range in Excel (18 Applications)
Hi, I have two queries.
1. How to use advanced filter by the criteria of Date? I mean if I want to filter data of a certain month how can it be done by using advanced filter?
2. How to use advanced filter by the contains criteria? suppose if I want to filter data which contains ‘letter A’ or have ‘5’ how can it be done?
thank you.
Hi Shilpa, thanks for the response. Here’s the solution to your question no 1
You can simply create a criteria similar to the methods of this article using dates. Suppose you want to see the sales information after May. Please watch the following image for the process. I created the criteria in G6 cell.
In order to solve the second question of your comment, please apply the method described in the Section 3.2 of this article.
Hope this helps to solve your queries.