Split single cell to multiple rows

Hi,


I'm trying to write a VBA and place it in an excel sheet so that VBA gets triggered on opening excel.
Curently what I have is:

Type   	Name	        Number
Cream	Test1	        40, do
Cream	Test21	20,last,80, no
Cream	Test30	no man, 20, value
Cream	Test8	        30,no way,yes

What I am looking for is:

Type   	Name	  Find Number
Cream	Test1	   40
                           do
Cream	Test2    20
                           last
                           80
                           no
Cream	Test30  no man
                           20
                           value
Cream	Test8	  30
                           no way

In result, the values of the third column are in different rows

Hi Jet
It's preferred to attach a sample of the desired results
Generally try this code (suppose your headers in row 1 and the target column is C)

Sub SplitIt()
    Dim Cell As Range, Arr, I As Long, J As Long
    For I = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
        If InStr(Cells(I, 3).Value, ",") > 0 Then
            Arr = Split(Trim(Cells(I, 3).Value), ",")
            For J = 0 To UBound(Arr)
                If J <> 0 Then Cells(I, 3).Offset(1, 0).EntireRow.Insert Shift:=xlDown
                Arr(J) = Trim(Arr(J))
            Next J
            Cells(I, 3).Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
        End If
    Next I
End Sub