Advanced Filter in Excel is one of the coolest options in Excel. Advanced Filter can do many complex filtering that AutoFilter can not do. In this article, we will see How to Use an Advanced Filter if the 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.
Excel Advanced Filter When Criteria Range Contains Text: 5 Ways
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: Advanced Filter with Criteria Range 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.
Read More: How to Use Advanced Filter with Wildcard in Excel
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.
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.
Read More: Advanced Filter with Multiple Criteria in Excel
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 Auto Filter and Advanced Filter in Excel
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.
Download Practice Workbook
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.
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.