Convert text to rows in Excel

  • Posted on: 16 April 2011
  • By: Antti

A client called up asking how to get a list of comma-separated values nicely formatted in Excel into individual rows. Although I don't often use or particularly enjoy Excel I thought it couldn't be too hard. Well, it isn't but it was still more complex than it ought to be. I solved the problem by using a macro to automatically convert the text to rows.

The problem

In my case the problem was converting hundreds of comma-separated email addresses into Excel rows.

I wanted to turn this:

email1@example.com, email2@example.com, email3@example.com

Into this:

email1@example.com
email2@example.com
email3@example.com

If you try and copy it over to Excel you end up with the email addresses in a single cell. Excel offers a text to columns wizard but sadly not one for rows.

The solution

As far as I know you have 2 ways to go about this. You can format the data manually by using the Excel UI or use a handy macro that does it for you automatically.

By hand

The best tutorial I found was at How-To Geek, explaining how to convert a row to a column and vice versa. It's a good thing there's a tutorial for it because I would've never figured it out myself. Besides being a bit fiddly you should be aware that there are limits to the number of columns you can have in Excel.

Excel version Column limit
Excel 2007 16 000
Excel 2003 256

By machine

This handly little macro will turn our comma-delimited text to rows in Excel.

Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub

Note that in my case the text was separated with a comma and a space. If your text doesn't match this you need to modify the highlighted part.

Now the only thing left to do is create a macro in Excel. If this is unfamiliar have a look at creating a macro in Excel 2003 or 2007 using the Visual Basic Editor.

Once you've created the macro highlight your cell and run the macro. That's it! A nice snippet to have in your toolbox.

Tags: