In excel TEXTSPLIT (array of arrays)
While TEXTSPLIT is a powerful tool for splitting text, it has limitations when dealing with ranges of cells.
When used on a single cell, it creates a single array containing the split values as expected.
However, using TEXTSPLIT on a range of cells like A5:A13 won’t create a two-dimensional array (array of arrays) as intended. Instead, it behaves similarly to the “=TEXTBEFORE(Text, delimiter)”, returning only the first value before the delimiter for each cell.
Here’s an alternative approach that works efficiently with large datasets without relying on concatenation:
✅
=LET(
rng, B7:B15,
IFERROR(
CHOOSECOLS(
TEXTBEFORE(
TEXTAFTER(” ” & rng, ” “, SEQUENCE(, MAX(LEN(rng)))) & ” “,
” “
),
SEQUENCE(
,
MAX(MAP(rng, LAMBDA(rng, COUNTA(TEXTSPLIT(rng, ” “))))),
1)),””))
One common solution involves concatenating the range with a unique delimiter (e.g., ““) using =TEXTSPLIT(CONCAT(A5:A13&”“),” “,”*”).
However, this approach can lead to a “Text Too Long” error for large datasets due to the concatenation.
💹 =DROP(REDUCE(“”,B7:B15,LAMBDA(x,y,IFNA(VSTACK(x,IFERROR( TEXTSPLIT(y,” “),””)),””))),1)
