Table of Contents
Excel’s “filter” function can be used to quickly sort data within a spreadsheet, and it can also be used to filter cells that contain multiple words. To do this, simply select the cells you want to filter, click the filter button, select the “Text Filters” option, and then choose “Contains” from the drop-down menu. You can then enter the words you want to filter by into the box, and the cells that contain those words will be filtered for you.
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:
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.