Split by Delimiter to New Rows

March 20, 2018 - by Bill Jelen

Someone built a worksheet with a repeating group of people in column B. This isn't the right way to build a spreadsheet. You've received the worksheet and would like to create a new row for each person listed in column B. The other columns (A & C) should be copied for each new row.

Amazingly, Excel can do this using the new Power Query tools. If you are in Excel 2010 or Excel 2013, download Power Query from Microsoft. If you are in Office 365, go to the Data tab and look for Get & Transform Data.

To convert the data, select the data (A1:C8) in this case. Select From Table/Range. Excel will display the Create Table dialog. Click OK.

You are now in the Query Editor. Click on the Crew heading to select that column.

From the Home tab in the Query Editor, open the Split Column drop-down and choose By Delimiter.

Initially, Power Query will detect that your delimiter is a semicolon. You want to click the triangle next to Advanced Options.

Under Advanced Options, choose to Split into Rows.

The data preview will show that you will have 47 rows of 3 columns. Click Close & Load to return the results to Excel.

The results are shown on a new worksheet inserted to the left of the original worksheet.

The amazing benefit: If you change any data in the original worksheet, you can re-run the query using the Refresh icon.

After a refresh, the new data will appear in the query results.

Power Query is amazing. Since it debuted after Excel 2013, it never achieved widespread fame. But it deserves to be famous.

Getting ready for the new features in Excel 2019.

Title Photo: Gian. / Unsplash