parse cell and insert rows?

[Gorrila Grod]

Is there a way in Excel to parse a cell that has a comma delimited
values, and put each value into rows? In this situation, the cell with
the delimited values can have a varying amount of values - and because
of formatting issues, I would like to insert new rows.

Example:

CELL A1: 10,11,12

CELL A2: 10
CELL A3: 11
CELL A4: 12

Any help would be greatly appreciated!

/grod

 

[Dave R.]

You can use text to columns to parse the string, then copy/paste
special>transpose to get them into rows.

 

[Dave Peterson]

You could use a little macro:

Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim mySplit As Variant
Dim myStr As String
Dim NumberOfElements As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
myStr = .Cells(iRow, "A").Value
If Len(myStr) > 0 Then
mySplit = Split97(myStr, ",")
NumberOfElements = UBound(mySplit) - LBound(mySplit) + 1
If NumberOfElements > 1 Then
.Cells(iRow, "A").Resize(NumberOfElements - 1) _
.EntireRow.Insert
.Cells(iRow, "A").Resize(NumberOfElements).Value _
= Application.Transpose(mySplit)
End If
End If
Next iRow
End With

End Sub
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

========
If you're using xl2k or higher, change this line
from:
mySplit = Split97(myStr, ",")
to:
mySplit = Split(myStr, ",")

And you can delete the split97 function, too.

Split was added in xl2k.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gorrila Grod]

Awesome! Thank you!

and thanks for the link on macros!