Table of Contents
In Excel, it is possible to filter cells that contain multiple words by using the “Contains” option in the filter menu. This allows you to search for specific words or phrases within a cell, and only display the cells that contain those words. This can be useful for organizing data and finding specific information within a larger dataset. By using this feature, you can easily narrow down your data to only show the cells that meet your criteria, making it easier to analyze and work with.
Excel: Filter Cells that Contain Multiple Words
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I filter cells in Excel that contain multiple words?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-filter-cells-in-excel-that-contain-multiple-words/
stats writer. "How can I filter cells in Excel that contain multiple words?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-filter-cells-in-excel-that-contain-multiple-words/.
stats writer. "How can I filter cells in Excel that contain multiple words?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-filter-cells-in-excel-that-contain-multiple-words/.
stats writer (2024) 'How can I filter cells in Excel that contain multiple words?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-filter-cells-in-excel-that-contain-multiple-words/.
[1] stats writer, "How can I filter cells in Excel that contain multiple words?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I filter cells in Excel that contain multiple words?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.