Convert text to rows in Excel
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.
In my case the problem was converting hundreds of comma-separated email addresses into Excel rows.
I wanted to turn this:
firstname.lastname@example.org, email@example.com, firstname.lastname@example.org
email@example.com firstname.lastname@example.org email@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.
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.
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|
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.
Once you've created the macro highlight your cell and run the macro. That's it! A nice snippet to have in your toolbox.