Saturday, October 8, 2011

Split the data of a cell into multiple cells

If you want to split the text of a cell into multiple cells. Snapshot below-

Here is the code-

Sub split_cell_text()
Dim i As Long, j As Long, k As Long
j = 2
' last filled row of a col a
For i = 2 To Range("a65356").End(xlUp).Row
' chnage "b" as pe the column name of data
' comma sign replace "," as pe ur the seperator used by you
splt = Split(Application.WorksheetFunction.Trim(Range("b" & i).Value), ",")

For k = LBound(splt) To UBound(splt)

Sheets(1).Range("f" & j).Value = Range("a" & i).Value
Sheets(1).Range("g" & j).Value = splt(k)

j = j + 1
Next k
Next i
End Sub

Download Sample Workbook

No comments:

Post a Comment