Hi,

I put some code together and by the time I finished I see you already have a
reply. However I will post my code also and you then have a choice.

The code parses the comma separated values using Text to Columns and then
copies the data and transposes to another worksheet. It then copies the
Number field.

Sub Macro2()
Dim wsSht1 As Worksheet
Dim wsSht2 As Worksheet
Dim rngColB As Range
Dim rngRows As Range
Dim rngDest As Range
Dim c As Range

Set wsSht1 = Sheets("Sheet1")
Set wsSht2 = Sheets("Sheet2")

wsSht1.Select
Columns("B:B").TextToColumns Destination:=Range("B1"), _
   DataType:=xlDelimited, _
   Comma:=True
       
wsSht2.Range("A1") = "NUMBER"
wsSht2.Range("B1") = "RESOURCE"

With wsSht1
   Set rngColB = Range(.Cells(2, 2), _
       .Cells(.Rows.Count, 2).End(xlUp))
End With

With wsSht1
   For Each c In rngColB
       Set rngRows = Range(c, .Cells(c.Row, _
           .Columns.Count).End(xlToLeft))
       Set rngDest = wsSht2.Cells(Rows.Count, 2) _
           .End(xlUp).Offset(1, 0)
       rngRows.Copy
       rngDest.PasteSpecial Paste:=xlPasteAll, _
           Operation:=xlNone, SkipBlanks:= _
       False, Transpose:=True
       c.Offset(0, -1).Copy _
       Destination:=rngDest.Offset(0, -1) _
       .Resize(rngRows.Columns.Count, 1)
   Next c
End With
wsSht2.Select
Range("A1").Select
End Sub

Regards,

OssieMac