How to Separate Multiple Values in One Excel Cell for Searching
Question details
The user needs a method to separate combined or multiline data within a single cell into individual rows so the data can be properly searched, filtered, and summarized.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing unnormalized data where multiple data points are grouped into single cells, preventing standard data analysis.
- Observed behavior
- Data is trapped in single cells as multiline strings, making it impossible to perform standard functions like deduplication, accurate filtering, or pivot table summarization.
Identify the delimiter separating your values (such as a comma, semicolon, space, or line break) and ensure your data is formatted as a structured table.
Split Cell Values into Rows using Power Query
Power Query is the most robust and recommended method for transforming multiline or combined cell values into separate, searchable rows without altering the original dataset.
By utilizing Power Query, you can establish an automated ETL (Extract, Transform, Load) process. This means whenever new combined data is added, a simple refresh will apply the splitting logic automatically, ensuring your data remains analysis-ready for pivot tables and deduplication.
Select any cell within your data range, navigate to the 'Data' tab on the Excel ribbon, 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 multiple values. Go to the 'Home' tab, click 'Split Column', and choose 'By Delimiter'.
Select the correct delimiter from the dropdown list (e.g., Comma or Custom for line feeds). Expand the 'Advanced options' section and select 'Rows' to ensure the separated values create new records vertically.
Click 'OK' to apply the split, then click 'Close & Load' in the upper-left corner to output your clean, separated data into a new worksheet for searching and filtering.

Use Text Filters for Direct Substring Searching
If you do not need to permanently separate the data and only want to find rows containing a specific value, you can use Excel's built-in Text Filters.
Easily Split and Filter Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing tools, including advanced Text to Columns and text filtering, allowing you to quickly organize multiline cells and search for specific values with a user-friendly interface.
- 1. Open your dataset: Launch WPS Office and open your spreadsheet containing the combined cell values.
- 2. Use Text to Columns: Highlight the column you want to split, navigate to the 'Data' tab, and click 'Text to Columns'.
- 3. Specify the delimiter: Choose 'Delimited' in the wizard, select your specific separator (like comma or space), and click 'Finish' to separate the data.
- 4. Search and Filter: Use the 'Filter' tool under the Data tab on your newly separated columns to quickly search, sort, or deduplicate your records.

Frequently Asked Questions
How do I split cells separated by a line break (Alt+Enter)?
In Power Query, when using 'Split Column By Delimiter', select 'Custom' from the delimiter dropdown. Check the box for 'Split using special characters' and select 'Line Feed' from the list. This tells the query to recognize the Alt+Enter line breaks.
Will splitting cells overwrite my original data?
If you use Power Query, your data is loaded into a completely new worksheet, leaving the original dataset untouched. However, if you use the traditional 'Text to Columns' feature directly on your sheet, it will split data into adjacent columns, which may overwrite existing data if you don't insert blank columns first.
Can I use formulas instead of Power Query to separate text?
Yes, in modern spreadsheet versions, you can use the TEXTSPLIT function (e.g., =TEXTSPLIT(A2, ",")) to dynamically split combined text across columns or rows using formulas. However, Power Query is more efficient for managing large datasets and complex multi-row transformations.




