How to split data into rows using Excel Power Query

Have you ever had a group of items separated by commas in Excel that you wanted to break into separate cells? Take the following example:

Here we have a list of signups for an upcoming office party by department and name. We’d like to easily sort and filter this data by name and department. In classic Excel, maybe you’d try using Text to Columns, which would result in something like this:

Not exactly what we want, right? It would be much easier to manage this data if it were in just two columns like this:

Power Query will make splitting our data just as easy as Text to Columns… except this does Text to Rows as well.

Splitting Signups by column

The first step is to load the rsvps table into Power Query. Check out this post for how to do that.

Under the Home tab of the Power Query editor, select Split Column > By Delimiter:

The “delimiter” is just a fancy word for what character is separating each item in your data. In our case, it’s a comma and Power Query will probably detect that for you. If it does not, select “Comma” under the delimiter menu.

Everything else is ready for you to hit OK… but before you do that, click Advanced Options. The option to do Text to Rows is hidden here! Click on Rows, then select OK.

This is looking promising, right?

Cleaning the leftover white space

It would appear that we’d be all set, but there’s actually on more thing to do here. To see that that is, go to the View tab of the Power Query editor ribbon and under Data Preview, check on “Show whitespace.”

It appears our data isn’t as clean as Power Query was letting on, huh?

Think back to the delimiter that we used here. It was a comma, but there is actually a comma and a space separating each name. We could fix this by adjusting our delimiter to include both characters, but let’s try something else.

Stripping the whitespace

We’ll instead strip the whitespace from the Signups column by clicking on its column header, then selecting Transform > Trim:

Great! The leading spaces are gone. And if you’re interested in the difference between Trim and Clean, check out this post.

We’re done! To see the results in Excel, go back to the Home tab of the Power Query editor, then Close & Load. You should see the exact results we were hoping for:

To compare your workbook to the solution file, click below:

I hope this post gave you yet another use case for the wonderful Power Query software. Check out my other posts on Power Query here.