Split cell contents separated by line feed into rows with #Excel #PowerQuery

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

Split cells with linefeed (Beginning)

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

Split cells with linefeed1.png

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

Split cells with linefeed

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

Split cells with linefeed2

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

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

Split cells with linefeed3

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.

Split cells with linefeed4

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

Split cells with linefeed5.1

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

Split cells with linefeed5

Here we go!

Split cells with linefeed0

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:Split cells with linefeed (end)

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. πŸ™‚

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... πŸ€” Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.