Will you always have 6 items in column B, or is this variable? If so,
what is the max no of items you could expect?

Approx how many rows of data do you have?

Pete

us10lh-goo...@yahoo.com wrote:

You could use a macro:

Option Explicit
Sub testme()

   Dim CurWks As Worksheet
   Dim NewWks As Worksheet
   Dim iRow As Long
   Dim FirstRow As Long
   Dim LastRow As Long
   Dim oRow As Long
   Dim mySplit As Variant
   Dim iCtr As Long
   Dim HowMany As Long
   
   Set CurWks = Worksheets("sheet1")
   Set NewWks = Worksheets.Add
   
   With CurWks
       FirstRow = 1 'no headers
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       
       oRow = 1
       For iRow = FirstRow To LastRow
           mySplit = Split(.Cells(iRow, "B").Value, ",")
           HowMany = UBound(mySplit) - LBound(mySplit) + 1
           NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
               = .Cells(iRow, "A").Value
           NewWks.Cells(oRow, "B").Resize(HowMany, 1).Value _
               = Application.Transpose(mySplit)
           With NewWks.Cells(oRow, "C").Resize(HowMany, 1)
               .Formula = "=row()+1-" & oRow
               .Value = .Value
           End With
           oRow = oRow + HowMany
       Next iRow
   End With
       
End Sub

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