ASM Arman
Filter in Excel is a great tool that you can use in a large worksheet to find out all the information that matches the criteria of the filter very quickly and efficiently. The filter can be applied to the entire worksheet, or one or multiple columns. You can apply a filter by choosing from a list that Excel will provide you while applying the filter or you can create a customized filter to match your needs. Using the Excel text filter you can search for texts using the Search box in the filter box or using the Text Filter option.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
5 Suitable Examples of Text Filter in Excel
Let’s assume a scenario where we have an Excel worksheet that has information about the products a company sold to the customers. The Excel worksheet has the Product name, Product Category, Sales Person that sold the product, and the Shipping address to deliver the product. Now we will use the text filter in this Excel worksheet to filter the text values.
1. Apply Excel Filter to Filter Specific Text from the Worksheet
You can filter specific text from a column of the worksheet. For example, we will use the Excel text filter to filter all the LED Monitors from the Product column.
Steps:
- First, we will select a cell in our data range, and then we will go to the Data. Next, we will select the Filter option from the Sort & Filter section.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header. We will click such a downward arrow on the Product column. A window will appear with options that you can use to filter the information in the Product column.
- You will see an option named Text Filters. Under the Text Filters option, names of all the unique products in the Product column are listed and each has a select box beside them. We will uncheck the Select All. We will then select only the LED Monitor option.
- Then will click OK.
- Now we will see the worksheet has only those rows that have LED Monitor as a Product.
Read more: How to Filter Unique Values in Excel
2. Use the Text Filter to Find Out Values that Equals Specific Text
We can also use the Excel text filter to find out values that equal or match a specific string of characters. In this example, we will filter all the rows which have Memory as the product Category using the Equals option of text filter.
Steps:
- First, we will apply filters to the columns in our worksheet. We will go to the Data tab and click on the Filter option from there.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header. We will click such a downward arrow on the Category. A window will appear with options that you can use to filter the information in the Category column.
- We will see a Text Filters option in that window. Upon clicking on that option, we will see another window with various types of text filters. We will click on the Equals.
- After clicking the Equals option, we will see a window titled Custom AutoFilter. This window has a drop-down menu to set the criteria for the Equals text filter. The default option is Equals. We will leave it like that for now.
- There is an input box just beside the drop-down menu. We will enter Memory in that input box as we want all the rows that equal or match Memory as the category.
- We will then click on OK.
- Now we will see the worksheet has only those rows that have Memory as Category.
- We can also change the criteria for the Equals text filter. There are also two options named And & Or just below the drop-down menu to set the criteria for the text filter. Below those options, you will find another drop-down menu to set the criteria for another Equals text filter. Options selected from these two drop-downs will give you different filtered results. But the result will depend on the option we select from the And & Or.
- For example, we have left the option from the first drop-down (equals) unchanged.
- Then we have selected the Or.
- From the second drop-down, we have again selected the equals.
- We will then click on OK.
- Now we will see the worksheet has only those rows which have Memory or Hardware as a product Category.
Similar Readings
- How to Filter Excel Pivot Table (8 Effective Ways)
- Filter Multiple Columns in Excel Independently
- How to Filter Multiple Rows in Excel (11 Suitable Approaches)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
3. Apply the Text Filter to Find Out Texts that Begins with Specific Characters
There is another type of text filter that we can use to filter rows that have text which begins with a specific character or set of characters. For example, we will filter all the rows which have Shipping Addresses that start with New. So we will find out all the rows where the Shipping Addresses are New York or New Hampshire.
Steps:
- First, we will apply filters to the columns in our worksheet. We will go to the Data tab and click on the Filter option from there.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header. We will click such a downward arrow on the Shipping Address A window will appear with options that you can use to filter the information in the Shipping Address column.
- We will see a Text Filters option in that window. Upon clicking on that option, we will see another window with various types of text filters. We will click on the Begins With.
- After clicking the Begins With option, we will see a window titled Custom AutoFilter This window has a drop-down menu to set the criteria for the Begins With text filter. The default option is Begins With. We will leave it like that for now.
- There is an input box just beside the drop-down menu. We will enter New in that input box as we want all the rows that have shipping addresses begin with New.
- We will then click on OK.
- Now we will see the worksheet has only the rows that have shipping addresses beginning with New.
- Like what we have seen in the Equals text filter, we can also change the criteria for the Begins With text filter. There are also two options named And & Or just below the drop-down menu to set the criteria for the text filter. Below those options, you will find another drop-down menu to set the criteria for another Begins With text filter. Options selected from these two drop-downs will give you different filtered results. But the result will depend on the option we select from the And & Or.
4. Perform the Text Filter to Find Out Texts that Contains Specific Set of Characters
We can use the text filter to filter all the rows that contain a specific character or a set of characters. For example, we will filter all the rows where the sales persons names have O as the second character of the name.
Steps:
- First, we will apply filters to the columns in our worksheet. We will go to the Data tab and click on the Filter option from there.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header. We will click such a downward arrow on the Sales Person A window will appear with options that you can use to filter the information in the Sales Person column.
- We will see a Text Filters option in that window. Upon clicking on that option, we will see another window with various types of text filters. We will click on the Contains
- After clicking the Contains option, we will see a window titled Custom AutoFilter This window has a drop-down menu to set the criteria for the Contains text filter. The default option is contains. We will leave it like that for now.
- There is an input box just beside the drop-down menu. We will enter “?o*” in that input box. The question mark (?) before o will just match only one character before o. And the asterisk (*) mark will match a series of characters or zero. That means the text filter will find out those cells in the Sales Person column where the names have o as the second character and only one character just before it. It can contain a series of characters just after the o.
- Then we will click on OK.
- Finally, we will see the worksheet has only the rows where the sales persons names have o as the second character.
5. Introduction to the Custom Text Filter in Excel
There is another option named Custom Filter in the text filters. You can use it to customize any of the text filters above. using Custom Filter, you can select different options from the text filter drop-down menus. We will use the Custom Filter to find out the shipping addresses that start with Ca.
Steps:
- First, we will apply filters to the columns in our worksheet. We will go to the Data tab and click on the Filter option from there.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header. We will click such a downward arrow on the Shipping Address A window will appear with options that you can use to filter the information in the Shipping Address column.
- We will see a Text Filters option in that window. Upon clicking on that option, we will see another window with various types of text filters. We will click on the Custom Filter.
- After clicking the Custom Filter option, we will see a window titled Custom AutoFilter This window has two drop-down menus as we have seen before to set the criteria for the Custom Filter. We will select equals for the first one and write C* in the input box just beside it. We will select does not equal for the second one and enter “?h*” in the input box just beside it. We will also select the And option.
- So the equals in the first drop-down menu will find out all the shipping addresses that start with C.
- And the doest not equal in the second drop-down menu will exclude all the shipping addresses that have “h” as the second character.
- We have 3 distinct shipping addresses that start with the character C. Those are Carolina, Chicago, and California.
- Chicago has the “h” as its second character. So does not equal in the second drop-down menu will filter it out.
- Then we will click on OK.
- Finally, we will see the worksheet has only the rows where the shipping addresses are either Carolina or California.
Things to Remember
- You can also use Does Not Equal, Ends With, Does Not Contain text filters.
- Does Not Equal is the opposite of Equals text filter. It will exclude or filter out the values that equal or matches the text we will give as input.
- Ends With with showing the values that end with the character or set of characters we give the filter as input.
- Does Not Contain will show the values that do not contain a character or a set of characters. It will exclude the values that contain the character or the set of characters we give the filter as input.
Conclusion
In this article, we have learned to use the Excel Text Filter to filter text values in different ways. I hope from now on you can use the Excel Text Filter easily to filter the text values in a worksheet. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
