Excel: Split Delimited Data into New Rows

November 1, 2017

An attendee at an IMA® chapter seminar I was presenting at recently asked about a process that would take delimited data in one column of a table and create new rows of data. Text to Columns would break the data out of the one column, but it wouldn’t create new rows. The solution is Power Query.


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.




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.




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.




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




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






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.




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.




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



17 + Show Comments
    kool February 24, 2021 AT 7:02 pm

    i love you, this has helped me so much

    martin December 24, 2020 AT 10:20 am

    How to increase the limit to seperate elements of a column into rows. I have at least 700 values seperated by commas, when I try the split to rows, its only doing the first 22 and trim/drop the other values

    Bjørn October 7, 2020 AT 2:54 pm

    Great article.
    It helped a lot.

    Ziyu Lu June 2, 2020 AT 11:16 am

    Does anyone know if there is a way to split them if the data has a carriage return? My data is not separated by commas.

    SourceMixer March 17, 2020 AT 5:37 pm

    Thank you!!!! This article saved my life.

    Martin Morrey November 14, 2019 AT 10:44 am

    Extremely clear article, thank-you!

    Andrew October 4, 2019 AT 7:36 pm

    Worked for my needs as well. Thank you!

    John Ly (AU) September 16, 2019 AT 8:39 pm

    Thanks so much for your clear and concise instructions, it helped me with my problem!!!

    ion April 27, 2019 AT 3:47 am

    Thank you very much!
    brilliant solution to my problem, perfect instructions.

    Shakeer March 12, 2019 AT 6:18 am

    i want the same process to be done by using VBA…please help

    Stephanie Hua June 7, 2018 AT 9:06 pm

    You’re the best, bill!
    10/10. give this man a raise.

    Bill Jelen February 9, 2018 AT 4:13 am

    Split to Rows is a new feature released in the summer of 2017. You will see it now if you are an Office 365 subscriber. If you purchased Office 2016 without subscribing to updates through Office 365, you will have to wait until Excel 2019 to be released in the second half of 2018. (Note that I don’t recommend buying Excel 2019. Every feature to be released in Excel 2019 is already available in Office 365 today.)

    shankar February 7, 2018 AT 6:22 am

    The advance option after selecting the column, doesnt show “Rows & Columns”. Instead it is showing “Number of columns to split into” and “Quote Style”. But i need options for “Coloumn”/”rows’. How to get it ?

    Jonathan Ortiz December 6, 2017 AT 2:58 pm

    Does anyone know which version of Power Query has the row feature? I have the same problem, and was happy to find out about power query, but the row feature is not available on the advance options for split column on mine….??

    Mark Albertson November 22, 2017 AT 3:00 pm

    Do you provide the Data Sets shown in the books.
    FYI, I use to buy and read you books cover to cover dog earing for future reference hard to keep up with you.

    Edward Stangler November 17, 2017 AT 10:06 pm


    Scott Ferguson November 3, 2017 AT 6:22 pm

    power query in excel is an awesome tool. I have been using power bi since it launched in 2015 and the ability to get, transform, visualize and distribute dashboards to any device without relying on programmers has been a game changer!

Comments are closed.

You may also like