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

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



"Gorrila Grod" wrote in message
news:2004110210394316807%grod@nothankscom...
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

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 wrote:

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 Peterson

Awesome! Thank you!

and thanks for the link on macros!



On 2004-11-02 17:57:59 -0700, Dave Peterson said:

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 wrote:

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