How to Filter Excel Rows by Individual Items in a Multi-Value Cell in WPS Spreadsheets
Question details
A user needs to filter spreadsheet rows by one individual category when each row may contain multiple comma-separated categories in a single cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an Excel column where each row contains multiple categories such as Brexit, Insurance, and Updates, and wants to filter every row containing one specific category.
- Observed behavior
- The standard AutoFilter treats each concatenated combination as a separate value, so choosing an individual category like Brexit is not directly available.
Open the workbook in WPS Spreadsheets and confirm that the target column has AutoFilter enabled by selecting the header row and clicking Home > AutoFilter. Check how the multi-value items are separated, such as by commas, so you can use the most suitable filter method.
Filter Rows Containing an Individual Item with Text Filters Contains
Use this method when you need to filter all rows that include one category text anywhere in a cell, even if the cell contains other categories.
WPS Spreadsheets treats a cell with multiple comma-separated items as one text value in AutoFilter, but the Text Filters Contains condition can match part of that text without changing the original data.
Select the header cell of the column that contains the combined categories, then click the AutoFilter arrow that appears.
In the filter menu, choose Text Filters > Contains. If your version shows Custom Filter instead, open it and set the condition to Contains.
Type the item you want to filter for, such as Brexit, Insurance, or Updates, and click OK.
WPS Spreadsheets now displays only the rows whose multi-value cell contains that text. Clear the filter or repeat the steps to view a different category.
Select Multiple Combination Entries Using Add Current Selection to Filter
Use this workaround when you want to manually select several exact combinations that appear in the filter list and all include the same category.
Restructure the Column So Each Category Occupies Its Own Row
Use this long-term solution when you need true item-level filtering, cleaner analysis, and more accurate reports.
Filter Individual Items in a Multi-Value Cell with WPS Spreadsheets
WPS Spreadsheets can open your Excel workbook and use Text Filters > Contains to show every row that includes one category, without rewriting formulas or changing file compatibility.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, then open the Excel file containing the multi-value column.
- 2. Enable AutoFilter: Select the header row and click Home > AutoFilter to display filter arrows if they are not already visible.
- 3. Use Text Filters Contains: Click the filter arrow in the multi-value column, choose Text Filters > Contains, type the individual item such as Brexit, and click OK.
- 4. Review and switch items: WPS Spreadsheets filters all rows containing that text. Clear the filter or repeat the steps to filter by a different item.
Frequently Asked Questions
Can Excel filters split comma-separated values in a cell automatically?
No. Standard AutoFilter treats the entire cell as one value. To filter by one item inside the cell, use Text Filters > Contains or restructure the data so each category has its own row.
How do I filter rows that contain Brexit even if the cell also contains other categories?
Click the filter arrow in the column, choose Text Filters > Contains, type Brexit, and click OK. WPS Spreadsheets will show all rows where the cell contains that text.
Does WPS Spreadsheets support the same AutoFilter options as Excel?
Yes. WPS Spreadsheets opens Excel workbooks and provides AutoFilter, Text Filters, Custom Filter, and similar filtering tools while maintaining compatibility with .xlsx and .xls files.
What is the most reliable permanent fix for filtering by individual categories?
Store one category per row in a dedicated column. You can split existing comma-separated cells using Data > Text to Columns and then reorganize the results into individual rows.
Can I select multiple individual items directly from the standard filter dropdown?
The standard dropdown lists whole cell combinations, not individual comma-separated items. You can use Add current selection to filter to select multiple exact combinations, but it does not split individual items.
