Split Delimited List into Rows

This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In the first post, we combined rows into a delimited list. In this post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s get to it!


Let’s say our export contains a delimited list of values, as shown below.

But, we need to split the delimited list and put them into rows, like this:

Each OrderID may have a variable number of rows, for example, some orders have 4 rows and some 3. We’ll use Power Query to help us out as follows:

  • Get the table into Power Query
  • Do the transformations
  • Return it to Excel

Let’s just jump right in.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Get the table into Power Query

To get the data table into Power Query, we select any cell in the table and select the Data > From Table/Range command. The Power Query window is displayed, as shown below.

Now, it is time to perform the transformations.

Do the transformations

The Custom column for each order contains one or more attribute:value pairs, separated by a colon. Each pair is separated by a semicolon. We want to see each attribute:value pair in one row and two columns. That means that we really need to split the Custom column twice. First, we’ll split the column at each semicolon into rows. Then, we’ll split the column at each colon into columns.

First, let’s split the column at each semicolon into rows. Bill Jelen mentioned this in his year-end tips for the MyExcelOnline podcast, and if you’d like to check it out, it is here.

We select the Custom column, and then the Transform > Split Column > By Delimiter command. The Split Column by Delimiter dialog opens, and we select the Semicolon delimiter, expand the Advanced options, and pick Rows, as shown below.

We click OK, and bam … the results are shown below.

Now, we need to split the Custom column again, this time at each colon into columns. So, we once again click the Transform > Split Column > By Delimiter command. We select Colon, as shown below.

We click OK, and bam…

With the hard part done, it is time to send the results to Excel.

Return it to Excel

To return the results to Excel, we use the Home > Close & Load command. The workbook is shown below.

And, the best part is that we don’t need to go through all that trouble tomorrow, next week, or next month when we export an updated table. We just right-click the green results table and hit Refresh. Nice 🙂

If you’d like to practice, feel free to download the sample file below.

If you have any related Power Query tips, please share by posting a comment below … thanks!

Sample File


Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Davy on October 2, 2020 at 2:39 pm

    This was VERY helpful for a project I had. Thanks very much!

    • Jeff Lenning on October 2, 2020 at 2:40 pm

      Thanks Davy … glad it helped 🙂

  2. Ron on March 8, 2021 at 1:25 am

    When performing a power query the resultant data output has each header and data in same cell, separated by a colon. eg state:”CA”, So how do I :
    (A) Remove the colon in each cell,
    (B) Place Headers in row 1,
    (C) Place data associated to each header in row 2.

  3. Julie Prewitt on January 19, 2023 at 6:39 pm

    I don’t see the “From Table” option in the Data section of Excel for Mac. Do you have instructions for how to do this on mac?

    • Jeff Lenning on January 22, 2023 at 11:58 pm

      I don’t have a version of Excel for Mac handy, but I think you go to: Select Data > Get Data > Excel Workbook.
      More info in this Microsoft article.

Leave a Comment