How to Split a Cell into Rows in Excel Using Power Query

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Whether your data has been downloaded into an ugly-looking format or someone decided to use their A1 cell to keep meeting notes, jampacked cells are an everyday problem Excel users experience. But the good news is that Excel has a tool called Power Query that makes this frustrating surprise fixable in two minutes or less.

Don’t let this venture into a new realm of Excel scare you. It’s easier than you imagine. And I’m going to teach you how to do it.

Excel Power Query: How to Split Cells into Rows

Here’s the sample data you can follow along with.

First, let’s look at the data we’ll be working with. We have a simple field of data that isn’t so scary. But look at the last column. We’ve got several projects listed in one cell. Let’s use Excel’s Power Query to fix this by splitting the projects into different rows.

original data 1

To start, we’re going to select our entire matrix of data. Then click the Data tab and choose “From Table/Range.” A window appears and asks us to create a table because this is raw, unformatted data. Make sure the data range in the box is correct and check or uncheck the header option depending on your file. In our case, our data has headers, so we’ll leave it checked. 

headers option 2

Hit ok, and your data will be transformed into a table, and Power Query will launch in a separate window.

You’ll notice that your fields with headers and individual projects have been placed in a table format, but your last column still has that long mess to it. 

power query view 3

To split those cells, click on the column header you want to split, not the entire table. From here, click the Split Rows under the Home tab and select “By Delimiter.” You can also find this option under the Transform tab or by right-clicking the header of your column.

A new options window appears where you can customize the split function. 

split settings 4

The delimiter is defaulted to “Space,” but looking back at our data, we can see that our delimiter is a comma followed by a space. Including an extra space after the comma is necessary because it tells Power Query to split the data into a new row at the start of the following text string.

Open the drop-down menu to enter the correct delimiter. You’ll see “Comma” as an option, which won’t account for our extra space. Instead, choose custom, and a new text box opens below where you’ll enter your comm and space. 

Leave the “Split at” section as it is; you want every instance of a comma and space to signify a new row. Next, open the advanced settings and change the “column” radio button to “rows.”

Click “Ok,” and you’ll see your fancy new table with the “Project” column split into the subsequent rows. Even better, all the other fields have been copied to make your data more complete and functional.

complete file for split 6

Lastly, click the “Close and Load” button under the Home tab. Power Query will close, and your table will populate in a new tab of your worksheet.

I told you it was easy! Try it out a few times, and you’ll wonder why you never learned this before.

Leave a Comment