Bit more than two years after the announcing of text functions, they are still discussing here. Mainly due to split of the range, more globally array of arrays issue. Below are collected variants. Majority of them were published shortly after text functions introduction in different sources. Some are definitely suggested by PeterBartholomew1 . Anyway, no references here, no one is my solution.
So, did I miss something? If not to consider different implementations of the same approach.
TEXTSPLIT.range = LAMBDA( data, [separator], [variant],
LET(
sep, IF( ISOMITTED(separator), ",", separator),
var, IF( ISOMITTED(variant), "A", variant),
rowSep, UNICHAR(9999),
maxSeps, MAX( LEN(data) - LEN(SUBSTITUTE(data, ",",)) +1),
nRows, ROWS(data),
/* most common one. Join rows to one string and split
by rows and columns then. The only such string
has size limit */
variantA, LAMBDA(
IFNA(
TEXTSPLIT(
TEXTJOIN(rowSep,,data),
sep, rowSep)
, "")
),
/* split every row on elements where each next column starts
from next element. Iterate by columns, TEXSPLIT returns
only first element for each column */
variantB, LAMBDA(
IFERROR(
TEXTSPLIT(
TEXTAFTER(
sep & data & sep,
sep,
SEQUENCE(,maxSeps)
)
, sep)
, "")
),
/* each row element expand to max size of all elements.
Split by MID on that size iterating by MAP on rows */
variantC, LAMBDA(
LET(
maxLength, MAX(
MAP(data,
LAMBDA(str, MAX(LEN(TEXTSPLIT(str, sep))) ) )
)-1,
fixSize, LAMBDA(str,
CONCAT(
LEFT( TEXTSPLIT(str, sep) & REPT(" ", maxLength), maxLength))
),
MID( MAP(data, fixSize),
SEQUENCE(,maxSeps,,maxLength),
maxLength
)
)
),
/* poor unpacking of 2D array with REDUCE/VSTACK
Good in debugging and maintenance, no so good
from performance point of view */
variantD, LAMBDA(
IFNA(
DROP(
REDUCE( "", range,
LAMBDA(_,v, VSTACK(_, TEXTSPLIT( v, sep) ) )
)
, 1)
, "" )
),
/* same 2D array unpacking but with thunks
which is much better form performance point of view */
variantE, LAMBDA(
LET(
pointers, MAP(data, LAMBDA(v, LAMBDA(TEXTSPLIT(v, sep)))),
clmId, IF(SEQUENCE(ROWS(data)), SEQUENCE(, maxSeps)),
ref, IF(SEQUENCE(, maxSeps), pointers),
res, MAP(clmId, ref, LAMBDA(n, P, INDEX(P(), n))),
IFERROR(res, "")
)
),
SWITCH( var,
"A", variantA(),
"B", variantB(),
"C", variantC(),
"D", variantD(),
"E", variantE(),
"?", "use any letter from A to E"
)
)
)