How to Filter Excel Cells by Individual Words or Areas
Question details
The user needs to filter a dataset based on individual words, such as specific locations, that are grouped together within single, multi-value Excel cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset to isolate specific target keywords that are trapped within comma-separated or multi-value cells.
- Observed behavior
- Currently, the names or locations are stored as combined strings in single cells, preventing standard drop-down filtering from isolating individual locations accurately.
Ensure your data is organized in a clear tabular format with distinct column headers before importing it into Power Query.
Use Power Query to Split Data into Rows
Splitting the column into rows using a delimiter isolates each value, making it fully compatible with standard Excel filters.
Power Query is the most robust tool for transforming multi-value cells. By instructing Excel to split delimited values into separate rows, you maintain all associated record data while enabling individual item filtering.
Select your data range in Excel, navigate to the Data tab, and click From Table/Range to open the Power Query Editor.
In the Power Query Editor, click on the header of the column containing the multi-value cells (e.g., the locations or practice areas).
Right-click the column header, select Split Column from the context menu, and choose By Delimiter.
Choose the appropriate delimiter (such as a comma). Expand the Advanced options section, select the Split to Rows radio button, and click OK.
Click the Close & Load button on the Home ribbon to return the separated data to a new Excel worksheet, where you can now easily filter by individual areas.
Use a PivotTable and Label Filter
If you prefer not to alter the dataset structure, you can use a PivotTable's built-in label filters to search for specific words within combined cells.
Filter Using SQL and Regular Expressions
For advanced users working with connected databases or online SQL tools, a regex filter can quickly isolate cells containing specific keywords.
Easily Filter and Analyze Complex Data with WPS Office
WPS Spreadsheet offers powerful built-in filtering tools, Text to Columns capabilities, and PivotTables to handle multi-value cells effortlessly. Enjoy a highly compatible, feature-rich spreadsheet environment to manage your data without complex setups.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your multi-value cells.
- 2. Enable AutoFilter: Select your header row, navigate to the Data tab, and click the AutoFilter button.
- 3. Select Text Filters: Click the filter drop-down arrow on the specific column header, select Text Filters, and then choose Contains.
- 4. Apply the keyword: Type the specific location or word you want to isolate (e.g., 'San Francisco') and click OK to instantly filter your view.
Frequently Asked Questions
Can I filter by multiple individual words at the same time in Excel?
Yes. By using Power Query to split the column into rows, you can use standard filters or slicers to check multiple individual words simultaneously. Alternatively, using the Custom AutoFilter allows you to set compound conditions like 'Contains Denver' OR 'Contains New York'.
Does splitting text to rows in Power Query alter my original data?
No. Power Query operates by creating a separate connection to your data. It applies your transformation steps and loads the result into a new table or worksheet, leaving your original source data completely untouched.
How do I handle cells with inconsistent delimiters, like commas and semicolons?
In the Power Query Editor, you can standardize your delimiters before splitting. Right-click the column, select Replace Values to change all semicolons to commas, and then proceed with the Split Column by Delimiter action.
Why can't I just use the standard Excel filter drop-down for this?
The standard filter drop-down treats the entire contents of a cell as a single unique text string. If a cell contains 'Denver, New York', it will not appear when you manually check 'Denver' in the list. You must use the 'Contains' text filter or separate the data first.
