Forum Discussion
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...
=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.