Forum Discussion
Using TEXTSPLIT without copy-fill-down
The same idea but with slightly different strengths and weaknesses.
TEXTBETWEEN
= LAMBDA(string, start, [end], [n],
LET(
end, IF(ISOMITTED(end), start, end),
k, SEQUENCE(1, n),
TEXTAFTER(TEXTBEFORE(string, end, k,,1), start,-1,,1)
)
)The start and end are separators that can be used to extract substrings. I have left it to the programmer to determine the number of terms to return.
Basically, though, I agree that your suggestion is a good way around the pitiful 'array of arrays' problem.
PeterBartholomew1 Good stuff. Looks like you got the syntax figured out for the AFTER/BEFORE arrangement (as opposed to BEFORE/AFTER). I did notice one little glitch, though... when the length of the delimiter is greater than the length of the value in the first field (before the first delimiter), it will return #VALUE!. Interestingly enough, a similar glitch occurred for me when I tried using the "Match to end" option (with a shorter version of TEXTTOCOLS), where it omitted the value in the last field if its length was less than the delimiter.
Another version of TEXTTOCOLS that does appear to work:
=LAMBDA(array,delimiter,
LET(
arr, delimiter&array&delimiter,
cols, SEQUENCE(, MAX(LEN(arr)-LEN(SUBSTITUTE(arr, delimiter, )))/LEN(delimiter)-1),
IFERROR(TEXTBEFORE(TEXTAFTER(arr, delimiter, cols), delimiter), "")
)
)
...but it relies on the IFERROR function to handle any #VALUE! or #N/A errors.
This all came about after responding to another post, Counting the number of occurrences of an entity in a table, where I used virtually the same technique to generate a unique list of items from a range of delimited strings with varying lengths. Sure, TEXTSPLIT/TEXTJOIN could've been used there, but I don't like the character limitations of that method.
I'm with you 100% that nested arrays should be allowed. Iterative alternatives, while useful, are typically not very efficient with larger datasets. In some situations, the right combination of dynamic array helper functions can be used to efficiently simulate an array of arrays, but it usually requires multiple steps involving various manipulations/transformations, which can be a tough sell for the average user. One such example can be found here: Multi-Row Data Consolidation Into Single Row, Multi-Column
Cheers! 🙂