Breaking several rows of data in single cell into a single cell per row

**Unajijua** · 02-17-2012, 03:00 AM

I have a spreadsheet where each cell contains tens of rows of data i.e. the kind of rows you get when you use Alt+Enter (see attached example). Is there an Excel formula that I can use to automatically move each row of data within the cell to its own cell? I have tried copying and pasting 'text only' but that causes the content of some rows to spill onto several cells. Will appreciate your help.

**JBeaucaire** · 02-17-2012, 03:33 AM

You can use Data > TextToColumns to split data by "visible characters", but the Alt-Enter (Char(10)) can't be used manually that way.

But, VBA can use the TextToColumns method and it CAN use that character to split the data. So this one-command macro will do it for you:

Sub TextToColumnsSpecial()
    
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(10), _
        FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.