How to Split Text to Rows in Excel in 2 Easy Steps

Sometimes the data we are working with in Excel is not always split out in the way we want. We may have a series of numbers or words for example that should be across multiple cells, but in reality are all sitting within a single one.

Assuming that there is a consistent delimiter between each value such as a space, comma, dash or so on then Excel has a powerful function called Text to Columns which automatically splits this out across columns – but what we don’t have is an equivalent funtion to split this over rows.

Luckily there is a quick workaround to this.

In order to split text to rows in Excel all you need to do is first run text to columns on our data, and then copy and paste using transpose to move this down rows instead.

Text to Rows Step 1 – Text to Columns

Our dataset for this one is quite simple – just a header value for year 1 vs year 2, and the associated values with them:

The first thing we need to do is split these out. To do so head up to the data tab, and towards the right select Text to Columns:

Next, assuming you have that common delimiter like a comma make sure to select Delimited:

From here simply select the delimiter we are using which in this data is the comma, and from here if you are working with basic text or numbers you can just click finish:

Depending on the data and formatting of the spreadsheet you may see a warning like this one. Assuming you dont have any data to the right of what we have selected you can press OK. If you do then just make some space with empty columns based on how many fields you are expecting.

Our output will now look like the below!

Now that we have our data split out, let’s look at how to make this work down rows instead of across columns.

Step 2 – How do we Split Text down Rows?

Our next step is very quick and easy.

First, highlight all of our data – in the example above this would be cells B3 down to H4, and either Right Click and Select Copy or Press CTRL + C to copy our data.

Next, choose where you want our data to sit and right click in the top left most cell. In the pop up menu that appears head down to Paste Special and select the paste option with the two arrows for Transpose.

What this will do is take our data and flip it so that the rows become columns and columns become rows.

When we click the transpose button we will now have the below output:

We now have every one of our values split out across individual rows! Even better still is it has kept the order of columns correct too.

All we really need to do now is wipe the top dataset, maybe remove the blank space above our new vertical headers and we have a nice clean data set that Excel has split individual cells into rows.

This sums up our guide on how to split text to rows and is a great example of two simple Excel functions working together to provide a solution that may not seem super obvious at first. For more Excel guides be sure to check out our other tutorials here.