Splitting data in one cell into separate rows

[akjha2]

Hi all,

I want to automatically split the 3rd column so that each row only has one number in the 3rd column.  Is there a way to do this without manually splitting each row? I am currently doing it by manually dragging the rest of the data several rows down to create space for the new rows and then copying in the data. 

[#2]

@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.

[#3]

@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.

[#4]

@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.

[#5]

@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...