Split data in a Cell to Multiple Rows using Power Query

Text to Tows

Data in one or more cells can be sent to multiple rows on the basis of delimiters (Comma, Space, etc.) using Power Query in Excel.

See the example below. Data in the cells C3:C6, which are separated with commas, are exported into the cells E3:F16.

I have used Power Query for this purpose and the procedure I followed is explained below.

Suppose you have a table like the one shown below. Categories are listed in the first column and all Items fall in each category are separated by a comma and listed against the corresponding category name.

Source Data

To normalize this data, Go to Data Tab in Excel ribbon and Click on From Table/Range

From Table/Range in the Data Tab

Create Table dialog is activated. Click OK to convert the selected data into an Excel Table.

Create Table Dialog

Power Query Editor is activated and source data is loaded in the editor.

Power Query Editor

Click on the small down arrow at the bottom of the Split Column button to see a list of available options. By DelimiterBy the number of characters and a few more options are listed here. To split the content of the cells on the basis of comma (,), select the column containing the Items and Click on By Delimiter.

Split Column in Power Query

In the Dialog for Split Column by Delimiter, Select Comma from the drop-Ddwn menu and Click OK

Split Column by Delimiter

Items that were separated with the comma in each cell are now in separate columns.

Select the first column and Click on Unpivot Columns in the Transform Tab

From the drop-down menu of Unpivot Columns, select Unpivot Other Columns.

Unpivot Other Columns, Power Query Editor

Now the data is Normalized or columns are Unpivoted. Items that were separated by a comma are now in different rows.

Normalized Data

Here the column called Attribute is not necessary and I will remove that column. Right-click on the column and select Remove Other Columns.

Remove Other Columns in Power Query Editor

Now we have the output which we are looking for and to load this data into the source sheet, Click on Close and Load To

In the Dialog for Import Data, specify the location where you want to place the output table and Click OK.

Here I will use the cell E2 of the same sheet.

The text data that were separated with commas are now in multiple rows of our worksheet. The advantage of Power Query is that you just need to refresh the query for the addition of new data and the table will update for that change.

I will add a new record to the source table.

Energy Drink – Monster, Red Bull, Rockstar Zero

To update the Output table, click on the Refresh button in the Data tab.

Newly added data i.e. the names of different energy drinks (Monster, Red Bull and Rockstar Zero) are appended to the output table.

Excel formula to split data into Rows and Columns