Power Query Problems

How to Separate Multiple Values in One Excel Cell for Searching

Phi Hung VoPhi Hung Vo Oct 28, 2026 872 views

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.

How to Separate Multiple Values in One Excel Cell for Searching
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.
Before you start

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.

Solution 1Recommended

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.

1
Load data into Power Query

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.

2
Initiate the split command

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'.

3
Configure the delimiter and split into rows

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.

4
Load the transformed data

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.

Split Cell Values into Rows using Power Query
Data Integrity Maintained: Power Query extracts and transforms the data into a new sheet, keeping your original raw data safely intact.
Efficient Data Processing

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. 1. Open your dataset: Launch WPS Office and open your spreadsheet containing the combined cell values.
  2. 2. Use Text to Columns: Highlight the column you want to split, navigate to the 'Data' tab, and click 'Text to Columns'.
  3. 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. 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.
Fully compatible with Microsoft Excel formats (.xlsx and .csv)Intuitive Text to Columns wizard for splitting complex delimitersAdvanced text filtering for quick data retrieval and analysisLightweight, fast, and completely free to use
microsoft office alternative - wps office

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.