Forum Discussion

akjha2's avatar
akjha2
Copper Contributor
Aug 03, 2024

Splitting data in one cell into separate rows

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 i...
excel
djclements's avatar
djclements
Silver Contributor
Aug 04, 2024

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

Resources