Filtering data based on a single criterion could be easier than filtering more than one criterion. For most Excel users, it tends to be a major issue while organizing data because it makes them even more anxious. That’s why they always seek help when Excel text filter multiple words has to be done.
Here, in this post, you will get to know how would you be able to handle such situations when users mostly get stuck.
Let’s dive in:
Cells having more than one word can be filtered by using the syntax given below:
=FILTER(A2:B8,ISNUMBER(SEARCH(“word1”, A2:A8))*ISNUMBER(SEARCH(“word2”, A2:A8)))
In the range A2:B8, this syntax can return the rows where the cells in the range A2:A8 has both “Word1” and “Word2.”
We have some other syntax you need to practice for Excel text filter multiple words.
Filter Cells Containing Multiple Words:
In our dataset, we have the title and years of experience for staff members of a sports team:
In the rows where the title has the word “Assistant” and “Coach” in the same cell, we will be using the following syntax to filter.
=FILTER(A2:B8,ISNUMBER(SEARCH(“Assistant”, A2:A8))*ISNUMBER(SEARCH(“Coach”, A2:A8)))
In the next image, you will get to see how this formula works in real:
The filter we are using in this syntax clearly filters the three rows containing the word “Assistant” and “Coach” in the same cell. Also, note that while filtering substrings can be used as well. for instance, to filter the rows we can apply the following formula where the title has the word “Assist” and “Coach” in the same cell:
=FILTER(A2:B8,ISNUMBER(SEARCH(“Assist”, A2:A8))*ISNUMBER(SEARCH(“Coach”, A2:A8)))
In the next step, you will see how this formula works in real:
Here you can see three cells having substring “Assist” and the full string “Coach” in the same cell are returned. Also note that in these examples, we have filtered those cells only that have more than one specific word. However, when the character asterisks (*) is used, it simply shows that you can filter cells having more than one specific word.
Excel Text Filter Multiple Words:
Filtering with multiple criteria is undoubtedly a tricky thing because it uses complex settings for criteria. The following data shows that we need to use filters on it.
And VS Or
When you need to filter the data that sticks together with all criteria at the same time, you may have to list the criteria in the given cells:
Now, you can open the Data tab and choose the Advanced option. From the Advanced Filter dialog box, you can check Filter the list, in place, and denote the List range and Criteria range. Below is the visual detail, have a look:
Now, click on the OK button. The process or filtering is finished, you can see the filtered data based on the criteria you need.
On the other hand, if you need to filter the data that meets any of the criteria, you can simply list the criteria just like the given detail:
Now, click on the Data tab and open the Advanced option. Identify the List range and Criteria range:
Press OK and see the filtered data you needed based on your criteria.
I hope that now you have a clear picture in your mind of Excel text filter multiple words query. Enjoy different functions in Excel and keep sharing each feature with others.
