Table of Contents
The Excel Advanced Filter feature allows you to use the “Contains” option to create a custom filter that will search for a specific text string within a range of cells. The filter can be used to easily isolate data containing the string, or to find and delete rows that contain the text. To use the Contains option, you must first select a range of cells to filter, then choose Advanced Filter from the Data tab, select the “Filter the list, in-place” option and finally use the “Contains” selection and enter the text string you are looking for.
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“:
