Excel: Split Delimited Data into New Rows

The member who asked the question receives a new file every day that contains a list of orders that came in through the website. Each order occupies one row in the worksheet. Column C contains a list of products in that order, with each product separated by a semicolon. The goal is to split the data in column C so that each product occupies a new row in the spreadsheet. All of the data from columns A, B, D, and E would be repeated in the new rows. In other words, rather than simply having each order take up a row, each product ordered would get its own row.

Text to Columns would let you easily split column C into new columns, but transposing those new columns to rows would probably require a VBA macro. Writing such a macro would require more than a couple of minutes and some knowledge of VBA instead of relying on the macro recorder. Fortunately, a 2017 update to Power Query now lets you split a column by a delimiter (or by a certain number of characters) and have the results appear in new rows.

POWER QUERY

It’s likely that you’ve been using Excel and have never heard of the Power Query tools. They were introduced after Excel 2013 shipped as a Microsoft Add-in for Windows versions of Excel 2010 and Excel 2013. These tools were originally written as a crutch for SQL Server pros who were forced to use Excel as a front end to Power Pivot. Microsoft found that Excel was too confusing for these database pros and created Power Query as a simple-to-use front end for transforming data in Excel.

In the early days, every tool that was in Power Query had already been in Excel. As Power Query evolved, however, it started getting new tools that perform actions that aren’t easy in Excel. For example, Power Query offers the Unpivot transform tool, which replaces a complicated trick using Multiple Consolidation Pivot Tables to turn monthly columns into rows.

If you’re running Office 365 or Excel 2016, you’ll find the Power Query tools in the Get & Transform Data group of the Data tab in the Ribbon. The group started out as the second group on the Data tab in Excel 2016, but people with Office 365 will notice that Get & Transform Data has been moved to the first group on the Data tab.

If you don’t see these tools on the Data tab, you can download Power Query for Excel 2010 or for Excel 2013 from Microsoft. Note that Power Query is only available for Windows versions of Excel. You can’t run Power Query on the iOS, Android, or Mac platforms.

CONVERTING THE DATA USING POWER QUERY

Say that this was a onetime conversion. The easiest method is to follow these steps:

1. Select one cell in your data and press Ctrl+T to convert the data to a table.
2. In the Power Query tools, choose From Table. The Power Query editor opens.
3. Select the column with your products. On the Home tab in Power Query, select Split Column, by Delimiter. Power Query will analyze your data and detect a lot of semicolons. It will guess that your delimiter is a semicolon.
4. In the Split Column dialog, click on Advanced Options.
5. In the Split Into section, choose Rows.

6. Click OK. The data preview will show that order 1234 now occupies eight rows in your data.

7. On the Power Query Home tab, click Close and Load. A new worksheet will appear to the left of the original worksheet. The product data will be split to rows.

REFRESHING THE QUERY

Power Query made it easy to transform the first data set. Even better is that Excel will remember the steps used in the transformation and can easily apply those steps tomorrow. Save the workbook.

When the new orders arrive tomorrow, paste them as part of the original table in the workbook. Select the result table (the table you created with Power Query), and a Queries and Connections panel will appear. A Refresh Icon appears to the right of the query. (If the panel isn’t wide enough, the Refresh icon might be hidden. Drag the left edge of the panel to make it wider.) Click the Refresh icon, and Excel will repeat the Split Column to Rows transformation. New data will appear in place of the second table.

IMPROVING THE WORK FLOW

If you need to process the orders every day, you can simplify the steps. Say that you download the orders once a day. The workbook has a single worksheet. Headings appear in Row 1. Data starts in Row 2. Save this workbook in a folder with the name “WebsiteOrders.xlsx.”

1. Start from a blank workbook. In the Power Query tools, choose Get Data, From File, From a Workbook.
2. Browse to the WebsiteOrders.xlsx workbook.
3. In the Navigator panel, choose Sheet1 from the Display Options dropdown. The data from the workbook will appear.
4. Click the Edit button in the lower right of the panel. In the Power Query Editor, perform steps 3 through 7 from above.
5. Right-click the Qquery Nname in the Queries and Connections panel. Choose Properties.
6. In the Query Properties dialog, choose Refresh Data When Opening the File.
7. Save the workbook with a name such “as PowerQueryToTransformWebsiteOrders.xlsx.”

In the future, the daily workflow becomes: Download a new orders file and save it to replace yesterday’s WebsiteOrders.xlsx file. Open the Power Query workbook. Excel will automatically load the new orders and split the columns to rows.

SF SAYS

The Microsoft Power Query tools might be the best feature added to Excel this century.