Table of Contents
“Contains” in Excel Advanced Filter is a useful function that allows users to filter data based on partial matches of a specific criteria. This feature is particularly helpful when dealing with large datasets that contain multiple variations of a keyword or phrase. By using “Contains”, users can easily narrow down their search and extract only the relevant data that meets their specified criteria. This advanced filtering technique can save time and effort in sorting through extensive data, making it a valuable tool for data analysis and organization in Excel.
Excel Advanced Filter: Use “Contains”
You can use the following syntax to filter for rows that contain specific text in an Excel Advanced Filter:
*sometext*
The following examples show how to use this function in two different scenarios:
- Filter for rows that contain one specific text
- Filter for rows that contain one of multiple text
Example 1: Filter for Rows that Contain One Specific Text
Suppose we have the following dataset that shows the total sales of certain products in certain regions for a company:
Now suppose we’d like to filter for rows where the Region contains “st” in the name.
To do so, we can define a criteria range:
Next, we can click the Data tab and then click the Advanced Filter button:
We’ll choose A1:C17 as the list range and F1:F2 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the Region contains the text “st“:
Example 2: Filter for Rows that Contain One of Multiple Text
Now suppose we’d like to filter for rows where the Region contains “st” or “Nor.”
To do so, we can define a criteria range:
Next, we can click the Data tab and then click the Advanced Filter button.
We’ll choose A1:C17 as the list range and F1:G3 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the Region contains “st” or “Nor“:
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use “Contains” in Excel Advanced Filter?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-contains-in-excel-advanced-filter/
stats writer. "How can I use “Contains” in Excel Advanced Filter?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-contains-in-excel-advanced-filter/.
stats writer. "How can I use “Contains” in Excel Advanced Filter?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-contains-in-excel-advanced-filter/.
stats writer (2024) 'How can I use “Contains” in Excel Advanced Filter?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-contains-in-excel-advanced-filter/.
[1] stats writer, "How can I use “Contains” in Excel Advanced Filter?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use “Contains” in Excel Advanced Filter?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.