Split multiple lines in multiple cells

cardmstr · Mar 22nd 2013

I have 3 Cells (A1, B1 and C1) each with 5 lines of text inside 1 cell. I would like to separate each line of text to it's own row respective of the columns... example attached with data in Cells A1, B1 and C1 and how I want the results to look from A3:C7.

forum.ozgrid.com/index.php?attachment/52319/

the number of cells will be longer and wider (AA:SS with up to 100 lines in each cell) but I can scale the code afterwards.

dangle · Mar 22nd 2013

Re: Split multiple lines in multiple cells

Hello cardmstr and welcome to Ozgrid

How about?

Code

Sub SplitCells()
Dim x As Long, z, rCell As Range
For Each rCell In Sheets("Sheet1").Cells(1).CurrentRegion
    If rCell <> "" Then
        For Each z In Split(rCell, vbLf)
            x = x + 1
            rCell.Offset(x + 2) = z
        Next
        x = 0
    End If
Next
End Sub

Display More

cardmstr · Mar 25th 2013

Re: Split multiple lines in multiple cells

Works great!! and it's way smaller than I thought it would be.
I don't entirely understand how it does what it does but I'll try to dig into it and reverse engineer it a bit so I can learn from it. Thanks again!

Split multiple lines in multiple cells

Participate now!

Share