After an in-house training, a colleague came to me and asked if there is a way to do the following:

The problem here is the contents in cells are separated by a line feed (line break).  A cell may contain two to five items and we want to split them into individual cells, into rows.

It reminded me of an old post How unorganized data could drive you crazy which I wrote about four years ago.  That’s the era before I know Power Query.

Although I used a trick of using Text to Columns to solve the problem before, this time the situation is different.  First, I want the cell contents to be split into rows, and unfortunately, we don’t have Text to Rows in Excel.  Second, we have more than one cells with this kind of unstructured data. 😦

This could be a nightmare for most Excel users.

But you know what, with Power Query, this can be solved at ease. 🙂

You may download a Sample File to follow along.

Note: All the screenshots in this post are coming from Excel 365.  If you are using Excel 2010/2013, Power Query has its own tab on the ribbon.  Moreover, you need to download and install Power Query for Excel as add-ins.  You may download Power Query for Excel in the following link: 

https://www.microsoft.com/en-hk/download/details.aspx?id=39379

If you are using Excel 2016 or later, Power Query is already built in and resides in Data tab, but renamed as Get and Transform.

A. Turn the range into Excel Table

1. Select any cell in the range of data, then press CTRL+T to insert Excel Tabl
2. Verify the data range is correctly captured
3. Check “My table has headers”
4. OK

B. Load the Table into Power Query

1. Select any cell of the Table
2. Go to the Data tab
3. Click From Table/Range

We should have the following Power Query Editor opened.

C. Let’s Split Column

1. Go to the Home tab of Power Query Editor
2. Split Column
3. Select By Delimiter

D. Tell Power Query how you would like to split the column

Make sure you have all the parameters (1-5 below) selected accordingly.

Note: If you do not see those “Advanced options”, your Power Query could be very “old”.  It’s time to download the latest version and install to your Excel (2010/2013).

WOW… Power Query did it like magic.

E. Let’s load the result to a Table on the worksheet

1. Select Table
2. Choose where you want to put the Tabel to
3. OK

Here we go!

As simple as this!

Indeed it is super easy IF we are dealing with one column only.

However, if we are dealing with two columns like this:

it is still easy with Power Query… just that we will need more steps to achieve so.  I think it’s better to demonstrate with a video, which I am going to post later here.

Please stay tuned. 🙂