Power Query Problems

How to Split Delimited Excel Cells into Rows using Power Query

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to convert thousands of Excel rows where specific cells contain multiple values separated by a vertical bar into individual rows, while duplicating the rest of the row's data.

Product: Excel Power Query
Device & OS: not provided
Scenario: Transforming and normalizing bulk Excel datasets where multiple variables are combined in single cells separated by a specific delimiter.
Observed behavior: Instead of manual extraction or standard column-splitting, the goal is to automatically split the cell contents by the delimiter to generate new rows for each value while retaining the adjacent column data.

Before you start

Ensure your dataset is formatted as an Excel Table (press Ctrl+T) before loading it into Power Query, and identify the exact delimiter character used in your cells.

Solution 1Recommended

Split Cells into Rows Using Power Query

The most efficient and native method to split delimited values into individual rows while duplicating adjacent column data.

Power Query is a powerful data transformation tool built into Excel. It allows you to cleanly split cells based on any standard or custom delimiter and automatically pivots the split data into new rows without the need for complex formulas or macros.

Load data into Power Query

Select your Excel table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Select the target column

In the Power Query Editor, click on the header of the column containing the delimited values to highlight it.

Choose Split Column by Delimiter

Go to the Home tab, click the 'Split Column' button, and select 'By Delimiter' from the dropdown menu.

Set the custom delimiter

In the dialog box, choose 'Custom' from the delimiter dropdown and type your specific character (such as a vertical bar '|') into the input field.

Apply row-splitting advanced options

Expand the 'Advanced options' section in the same dialog, select 'Rows' under the 'Split into' options, and click OK.

Export the transformed data

Click the 'Close & Load' button on the Home tab to export your newly transformed data back into a fresh Excel worksheet.

Data Normalization Complete: Your single delimited cells are now split into multiple rows, and the remaining column values have been correctly duplicated for each new record.

Free Microsoft Office alternative

Looking for a Lightweight, High-Performance Spreadsheet Tool?

While advanced Power Query Editor functions are specific to Microsoft Excel, WPS Office provides a highly compatible, feature-rich spreadsheet application. It easily handles massive datasets, perfectly opens Excel files, and offers powerful built-in data manipulation tools at no cost.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Includes essential data management tools like Text to Columns, advanced filtering, and PivotTables.Free, lightweight, and optimized to process thousands of rows quickly.Familiar tabbed interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard Text to Columns to split data into rows?

No, the standard 'Text to Columns' feature in Excel only splits delimited data horizontally into adjacent columns. To split data vertically into individual rows, you must use Power Query or complex array formulas.

How do I type a vertical bar as a custom delimiter?

In the 'Split Column by Delimiter' dialog, select 'Custom' from the dropdown list. Then, type the vertical bar symbol (|) into the text box. On most standard keyboards, you can type this by holding Shift and pressing the Backslash (\) key.

Will splitting the column into rows delete the data in my other columns?

No. When you use the 'Split into Rows' option in Power Query, Excel automatically duplicates the corresponding data in all other columns for each newly generated row, ensuring no associated information is lost.

Excel & Data Problems