You can use the following syntax to filter cells in Excel that contain multiple words:
=FILTER(A2:B8,ISNUMBER(SEARCH("word1", A2:A8))*ISNUMBER(SEARCH("word2", A2:A8)))
This formula will return the rows in the range A2:B8 where the cells in the range A2:A8 contain both “word1” and “word2.”
The following example shows how to use this syntax in practice.
Example: Filter Cells that Contain Multiple Words
Suppose we have the following dataset in Excel that contains the title and years of experience for staff members of a sports team:
We can use the following formula to filter for the rows where the title contains the word “Assistant” and “Coach” in the same cell:
=FILTER(A2:B8,ISNUMBER(SEARCH("Assistant", A2:A8))*ISNUMBER(SEARCH("Coach", A2:A8)))
The following screenshot shows how to use this formula in practice:
We can see that the filter only returns the three rows that contain the word “Assistant” and “Coach” in the same cell.
It’s important to note that we can also use substrings when filtering.
For example, we could use the following formula to filter for the rows where the title contains the word “Assist” and “Coach” in the same cell:
=FILTER(A2:B8,ISNUMBER(SEARCH("Assist", A2:A8))*ISNUMBER(SEARCH("Coach", A2:A8)))
The following screenshot shows how to use this formula in practice:
We can see that three cells are returned that contain the substring “Assist” and the full string “Coach” in the same cell.
Note: In each of these examples we filtered for cells that contained two specific words, but by using multiple asterisks (*) we can filter for cells that contain as many specific words as we’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Delete Rows with Specific Text
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if Cell Contains Text from List