Splitting data in one cell into separate rows

akjha2 As an alternative, here's a non-iterative dynamic array method for unpivoting delimited data with Excel for MS365:

=LET(
    item_count, LEN(C2:C8) - LEN(SUBSTITUTE(C2:C8, ",", )) + 1,
    cols, SEQUENCE(, MAX(item_count)),
    test, item_count >= cols,
    HSTACK(
        TOCOL(IFS(test, A2:A8), 2),
        TOCOL(IFS(test, B2:B8), 2),
        TOCOL(TEXTBEFORE(TEXTAFTER("," & C2:C8 & ",", ",", cols), ","), 2)
    )
)

Adjust the range references for each column as needed or use structured table references if your data is formatted as an Excel table.

Sample Results

Another variation of the same method using a single range reference could be:

=LET(
    a, A2:C8,
    b, INDEX(a,, 3),
    i, LEN(b) - LEN(SUBSTITUTE(b, ",", )) + 1,
    j, SEQUENCE(, MAX(i)),
    HSTACK(
        INDEX(a, TOCOL(IFS(i >= j, SEQUENCE(ROWS(a))), 2), {1,2}),
        TOCOL(TEXTBEFORE(TEXTAFTER("," & b & ",", ",", j), ","), 2)
    )
)

See attached...

akjha2 

Sub split_data()

Dim i, j, k As Long
Dim str() As String

Range("E:G").Clear

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

str = Split(Cells(i, 3).Value, ",")

For j = LBound(str, 1) To UBound(str, 1)
k = k + 1
Cells(k, 5).Value = Cells(i, 1).Value
Cells(k, 6).Value = Cells(i, 2).Value
Cells(k, 7).Value = str(j)
Next j

Next i


End Sub

In e.g. Excel 2013 you can run this macro when you click the button in cell I1.

akjha2 

A quick alternative with legacy Excel e.g. Excel 2013 could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The query works for up to 60 comma separated numbers in one cell.

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.

akjha2 

=LET(rng,A1:C6,

DROP(REDUCE("",SEQUENCE(ROWS(rng)),

LAMBDA(u,v,

LET(z,COLUMNS(TEXTSPLIT(INDEX(INDEX(rng,,3),v),",")),

VSTACK(u,

HSTACK(

IFNA(EXPAND(INDEX(INDEX(rng,,1),v),z),INDEX(INDEX(rng,,1),v)),

IFNA(EXPAND(INDEX(INDEX(rng,,2),v),z),INDEX(INDEX(rng,,2),v)),

TOCOL(TEXTSPLIT(INDEX(INDEX(rng,,3),v),","))

))))),

1)

)

If you work with Office 365 or Excel for the web you can apply this formula.