top of page

You are learning Sorting and Filtering in MS Excel

How to filter by a specific text string (e.g., containing "apple")?

There are two main ways to filter data by a specific text string in Excel:

Method 1: Using the Filter Dropdown Menu

1. Select the data you want to filter: Click on any cell within the data range you want to filter. This will ensure the filter options apply to the entire dataset.
2. Go to the Data tab: Click on the "Data" tab in the Excel ribbon.
3. Activate the Filter: Click the "Filter" button in the "Data Tools" group. Filter arrows will appear on the header row of your data.
4. Click the filter dropdown arrow for the text column: Find the column containing the text data you want to filter. Click the filter dropdown arrow next to the header cell of that column.
5. Select Text Filters: From the dropdown menu, choose "Text Filters."
6. Choose the filtering criteria: Several options will appear, allowing you to filter based on text characteristics. Here's how to find entries containing "apple":
- Select "Contains."
- In the text box next to "Contains," type "apple" (without quotation marks).
7. Click OK: Click the "OK" button to apply the filter.

Your data will be filtered to display only rows where the chosen text column contains the string "apple."

Method 2: Using Advanced Filter Criteria

1. Select a destination for filtered data (optional): If you want to copy the filtered results to another location, choose a blank area on your sheet and select the entire range where you want the filtered data to appear.
2. Go to Data tab: Click on the "Data" tab.
3. Click Advanced: In the "Data Tools" group, click the "Advanced" button.
4. Set up criteria range:
- In the "Criteria range" section, enter the following information:
- In the top row of a separate area (preferably hidden or on another sheet), type the header for your text column (e.g., "Fruits").
- In the cell below it, type the criteria for filtering, which is "contains ""apple"" " (including quotation marks).
Make sure the criteria range includes both the header and the filtering condition.
5. Copy criteria range: Select the entire criteria range you just created (including the header row). Copy it by pressing Ctrl+C.
6. Define copy location: Back in the "Advanced Filter" window, ensure the cursor is in the "Criteria range" box. Paste the copied criteria range using Ctrl+V.
7. Set copy range: In the "List range" box, enter the entire cell range of your data table, including the header row.
8. Copy range to output (optional): If you chose a destination in step 1, enter that cell range in the "Copy to" box. Otherwise, leave it blank to display the filtered data in place.
9. Click OK: Click the "OK" button to apply the advanced filter.

Using either method will result in your data being filtered to show only rows where the specified text column contains the string "apple".

bottom of page