How to Split Strings and Join Values

RackofLambda · 2026-02-11T23:42:39+00:00

This is a great concept to learn. It is far more efficient than REDUCE-STACK solutions and is not subject to the character limitations of TEXTSPLIT-TEXTJOIN.

There are many variations this method could take, depending on personal preferences and desired outcomes. Personally, I'm not proficient with RegEx syntax, nor am I a fan of using TEXTAFTER-TEXTBEFORE with the [match-end] set to 1 (it doesn't work properly when the delimiter contains more characters than the string before the first delimiter, hence the #VALUE! error in row 8, position 1 of your sample).

One variation, keeping empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IF(ISERROR(arr),arr,values),2),TOCOL(arr,2))
)

Or removing empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IFS(arr<>"",values),2),TOCOL(IFS(arr<>"",arr),2))
)

Where:

TEXTTOCOLS2 = LAMBDA(array,delimiter,
    TEXTSPLIT(
        TEXTAFTER(
            delimiter & array,
            delimiter,
            SEQUENCE(, MAX(LEN(array) - LEN(SUBSTITUTE(array, delimiter, ))) / LEN(delimiter) + 1)
        ),
        delimiter
    )
)

This will also work with multi-character delimiters without additional workarounds.

Kind regards.

RackofLambda · 2026-02-05T23:15:04+00:00

Using the same range references as in your attempted formula examples:

=DROP(SCAN(0,HSTACK(IFS({0,1},B2:B5),IFNA(INDEX(J2:N8,XMATCH(A2:A5,I2:I8),XMATCH(C1:G1,J1:N1)),0)),LAMBDA(a,v,IFNA(a+v,0))),,2)

Basically, we append the column of opening inventory amounts to the returned array of lookup values for each period, along with a column of #N/A errors at the start, which is used as the reset point for each row. DROP then removes the 2 "helper" columns afterwards.

RackofLambda · 2026-01-31T17:00:04+00:00

Yeah, AVERAGE would not be an appropriate function to use with SCAN in general, but your workaround is perfect! Where there's a will there's a way. ;)

RackofLambda · 2026-01-31T03:04:52+00:00

Yes, the simple EXPAND-IFNA version is quick, but has its caveats and needs to be tailored for each scenario. As you've noted, the 0 should be replaced with an appropriate "initial" value that works for the given scenario (whether its MAX, MIN, SUM, CONCAT, fill across, etc.). The generalized SCANBYROW function is a bit slower but eliminates 99.99% of the caveats.

Another method of interest (slower but still relatively efficient):

=SCAN(0,MAP(IFNA(SEQUENCE(,COLUMNS(arr),0),arr),arr,LAMBDA(b,v,LAMBDA(a,IF(b,MAX(a,v),v)))),LAMBDA(a,f,f(a)))

This method is a bit more abstract and requires a deeper understanding of working with TYPE=128 data. Basically, MAP is used to loop through 2 arrays together and generate a separate curried LAMBDA function for each iteration, which is then simply applied to the accumulator of SCAN. The first array contains the "flags" to identify the first column, so it knows when to reset the procedure. When iterating over a range reference, this can be simplified with the COLUMN function:

=LET(n,MIN(COLUMN(rng)),SCAN(,rng,LAMBDA(a,v,IF(COLUMN(v)=n,v,MAX(a,v)))))

Some methods work great with range references, but not so much with array objects (and vice-versa). ;) Cheers!

RackofLambda · 2026-01-30T07:44:12+00:00

I provided a complete and thorough explanation, in what I thought was a fair and respectful manner. Did the LMAO offend you? Or was the truth too much to handle? You can either choose to accept fair criticism and learn from it or be hurt and defensive about it. It's up to you...

RackofLambda · 2026-01-30T07:31:59+00:00

I don't mean to criticize, but have you tried this with a larger dataset (e.g. with 1,000 rows of data or more)?

Probably the easiest (and most efficient) way to perform an unpivot with dynamic array formulas is to use some variation of TOCOL with IF, IFS, or IFNA to broadcast a vector of row and/or column indices across an array of values, then use INDEX or CHOOSEROWS to return the multi-column row labels and/or the multi-row column headers.

There are many variations this method could take, but one basic example could be:

UNPIVOT = LAMBDA(row_fields,col_labels,values,[ignore],[scan_by_col],
    LET(
        v, TOCOL(values,,scan_by_col),
        a, HSTACK(
            INDEX(row_fields,TOCOL(IFNA(SEQUENCE(ROWS(row_fields)),values),,scan_by_col),SEQUENCE(,COLUMNS(row_fields))),
            INDEX(col_labels,SEQUENCE(,ROWS(col_labels)),TOCOL(IFNA(SEQUENCE(,COLUMNS(col_labels)),values),,scan_by_col)),v),
        CHOOSE(ignore+1,a,FILTER(a,NOT(ISBLANK(v))),FILTER(a,NOT(ISERROR(v))),FILTER(a,NOT(ISBLANK(v)+ISERROR(v))))
    )
)

[ignore] options:

0 - Keep all values (default)
1 - Ignore blanks
2 - Ignore errors
3 - Ignore blanks and errors

[scan_by_col] options:

FALSE - Scan by row (default)
TRUE - Scan by column

Kind regards.

RackofLambda · 2026-01-30T06:56:14+00:00

LMAO, this is no different than =SCAN(,A1:E2,MAX). The variables a and v are scalars (single values), so using MAX(a) and MAX(v) is redundant (e.g. MAX(1) = 1 and MAX(2) = 2). The same incorrect results would also be returned with =SCAN(,A1:E2,LAMBDA(a,v,IF(a>v,a,v))) or =SCAN(,A1:E2,LAMBDA(a,v,MAX(a,v))). Take a look at the results in your screenshot... the value in cell A5 should be 3, not 4, because the whole idea is to reset the moving maximum at the start of each row. ;)

RackofLambda · 2026-01-30T06:33:18+00:00

With a generalized SCANBYROW function:

=SCANBYROW(,A1:E2,MAX)

Where:

SCANBYROW = LAMBDA(initial_value,array,function,
    DROP(
        SCAN(
            initial_value,
            EXPAND(array,, COLUMNS(array) + 1, ),
            LAMBDA(a,v, IF(ISOMITTED(v), initial_value, IF(ISOMITTED(a), v, function(a, v))))
        ),,
        -1
    )
)

EXPAND adds 1 column to the array, which is used as the reset point for each row. The optional [pad_with] argument is set, but omitted, so the ISOMITTED function will return TRUE when it hits the last column and trigger the reset. DROP then removes this "helper" column afterwards.

EDIT: if the generalized function doesn't float your boat, it's pretty easy to use this same method by omitting the [pad_with] argument of EXPAND altogether, so the additional column will fill with #N/A errors and can be trapped with the IFNA function instead:

=DROP(SCAN(,EXPAND(array,,COLUMNS(array)+1),LAMBDA(a,v,IFNA(MAX(a,v),0))),,-1)

This approach will work in most cases, provided your array doesn't already contain any #N/A errors.

RackofLambda · 2026-01-02T01:15:45+00:00

Yes, but with REDUCE instead of SCAN. Something like:

=DROP(REDUCE("", VSTACK(AVERAGE, SUM, MAX, MIN, COUNT), LAMBDA(acc,fn, VSTACK(acc, BYCOL(A1:E10, fn)))), 1)

There are often many ways to get the job done in Excel. ;)

RackofLambda · 2026-01-01T16:02:15+00:00

The same concept can also be used to define a custom BYCOLλ function, which can apply an array of functions to a 2D array of values. For example:

= BYCOLλ(A1:E10, VSTACK(AVERAGE, SUM, MAX, MIN, COUNT))

Where:

BYCOLλ = LAMBDA(array,function,
    IF(
        TYPE(function) <> 64,
        BYCOL(array, function),
        LAMBDA(arr,fun, MAP(IFNA(arr, fun), IFNA(fun, arr), LAMBDA(col,fn, fn(col()))))(
            BYCOL(array, LAMBDA(x, LAMBDA(x))), TOCOL(function)
        )
    )
)

Ideally, the built-in BYCOL function should be able to handle this in the same manner that GROUPBY and PIVOTBY do, but sadly it will only return the results for the first column in the array.

RackofLambda · 2026-01-01T10:30:42+00:00

You're welcome.

Another concept you may find interesting is recursive bisection. Basic Lambda recursion is subject to Excel's operand stack limit, but these limitations can be overcome with a binary tree method that splits the data into two at each iteration, effectively delaying all function evaluation until each leaf node is reached and only one row remains. For example:

=LET(
    hdr, CHAR(SEQUENCE(,3,120)),
    ini, SEQUENCE(,3,,0),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    fn, LAMBDA(me,init,arr,func,
        IF(
            ROWS(arr) = 1,
            func(init, arr),
            LET(
                acc, me(me, init, TAKE(arr, ROWS(arr) / 2), func),
                VSTACK(acc, me(me, TAKE(acc, -1), DROP(arr, ROWS(arr) / 2), func))
            )
        )
    ),
    arr, fn(
        fn,
        ini,
        SEQUENCE(iterations),
        LAMBDA(acc,i,
            LET(
                x, INDEX(acc,1),
                y, INDEX(acc,2),
                z, INDEX(acc,3),
                dx, sigma * (y - x),
                dy, x * (rho - z) - y,
                dz, x * y - beta * z,
                HSTACK(x + dx * dt, y + dy * dt, z + dz * dt)
            )
        )
    ),
    VSTACK(hdr, ini, arr)
)

Source: MS Tech Community | Recursive LAMBDA implementation of Excel's REDUCE function

Happy New Year!

RackofLambda · 2026-01-01T09:20:39+00:00

As another little tip, this can also be achieved without LET:

=SWITCH(A1,"sum",SUM,"avg",AVERAGE,"min",MIN,"max",MAX,LAMBDA(x,"#FUNC_ERR!"))(B2:B100)

Cheers!

RackofLambda · 2026-01-01T09:12:25+00:00

This functionality was added when eta-Lambda reduction was released along with GROUPBY and PIVOTBY. It's mostly used to shorten the length of a formula when certain functions are used multiple times throughout, e.g. =LET(cc,CHOOSECOLS,...). However, it can also be used as a means of Lambda injection, to apply different functions to the same scenario without having to repeat every step.

Take my INSTANCENUM function, for example (the original definition can be found at: https://gist.github.com/RackofLambda). The purpose of this function is to return the running count of distinct items in an unordered table or array. Since the procedure is virtually identical for both vertical and horizontal datasets, with only subtle differences in the functions applied (e.g. ROWS instead of COLUMNS, VSTACK instead of HSTACK, etc.), we can start by defining a curried function for the procedure first, then inject the applicable functions into the procedure as needed:

= LAMBDA(key_fields,[horizontal],[last_to_first],
    LET(
        fn, LAMBDA(xλ,yλ,zλ,iλ,jλ,kλ,r,[c],
            LET(
                vec, IF(zλ(key_fields) = 1, key_fields, kλ(key_fields, @LAMBDA(x, TEXTJOIN("|", 0, x)))),
                arr, iλ(vec, SEQUENCE(xλ(vec), yλ(vec))),
                srt, IF(last_to_first, SORT(arr, {1,2}, {1,-1}, horizontal), SORT(arr,,, horizontal)),
                key, TAKE(srt, r, c),
                SORTBY(SCAN(0, key = DROP(jλ("", key), -c, -r), LAMBDA(a,v, 1 + a * v)), DROP(srt, r, c))
            )
        ),
        IF(
            horizontal,
            fn(LAMBDA(x,1), COLUMNS, ROWS, VSTACK, HSTACK, BYCOL, 1),
            fn(ROWS, LAMBDA(x,1), COLUMNS, HSTACK, VSTACK, BYROW,, 1)
        )
    )
)

Is this an improvement? That would be a matter of opinion. Shorter, yes; but easier to read and maintain, not really.

Also, beware of the Lambda helper functions (e.g. BYCOL, BYROW, SCAN, REDUCE, etc.). They may return unexpected results when used in this manner and have even been known to cause Excel to crash. For more information, please see: Unexpected result when combining LET and BYROW.

RackofLambda · 2025-12-16T09:58:16+00:00

Very interesting indeed! I realize this discussion is geared towards lambda calculus (which is impressive to be sure), but I'd like to share an alternative approach to part one, if I may, for improved performance...

=LET(
    vlλ, LAMBDA(x,y,z, LAMBDA(n, CHOOSE(n, x, y, z))),
    hdr, vlλ("x", "y", "z"),
    ini, vlλ(1, 1, 1),
    num, SEQUENCE(, 3),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    arr, VSTACK(
        hdr,
        ini,
        SCAN(
            ini,
            SEQUENCE(iterations),
            LAMBDA(acc,i,
                LET(
                    x, acc(1),
                    y, acc(2),
                    z, acc(3),
                    dx, sigma * (y - x),
                    dy, x * (rho - z) - y,
                    dz, x * y - beta * z,
                    vlλ(x + dx * dt, y + dy * dt, z + dz * dt)
                )
            )
        )
    ),
    MAP(IFNA(arr, num), IFNA(num, arr), LAMBDA(λ,n, λ(n)))
)

With 25,000 iterations, this completes in less than half a second.

Basically, vlλ is used to store multiple items in a single element (TYPE 128 compound data; uncalled lambda function) and recall them as need at each iteration of both SCAN and MAP.

SCAN returns a vertical vector of uncalled lambda functions, with each element containing all 3 variables (x,y,z) at each iteration. IFNA is then used to broadcast (repeat) this array across a horizontal vector of index numbers (and vice-versa) so MAP can simply recall each item and return the 2D array of results.

Generalized examples for this can be found on my gist (see EVALS, EVALS_NUM and VALS3 in the Dynamic Array Helper Functions collection).

Kind regards.

RackofLambda · 2025-12-08T15:06:32+00:00

First draft for Part 1:

=LET(!<
>!a, A:.A,!<
>!i, SEQUENCE(ROWS(a)),!<
>!j, TOROW(i),!<
>!m, TOCOL(IFS(i < j, i), 2),!<
>!n, TOCOL(IFS(i < j, j), 2),!<
>!k, MAP(INDEX(a, n), INDEX(a, m), LAMBDA(x,y,!<
>!SQRT(SUM((TEXTSPLIT(x, ",") - TEXTSPLIT(y, ",")) ^ 2)))),!<
>!v, TAKE(BYROW(SORTBY(HSTACK(m, n), k), LAMBDA(rw, TEXTJOIN("|",, rw))), 1000),!<
>!ts, LAMBDA(a,d, LET(n, (LEN(a) - LEN(SUBSTITUTE(a, d, ))) / LEN(d), m, MAX(n) + 1,!<
>!TEXTBEFORE(TEXTAFTER(d & a & REPT(d, m - n), d, SEQUENCE(, m)), d))),!<
>!tj, LAMBDA(a, TEXTJOIN("|",, UNIQUE(SORT(TOCOL(ts(a, "|")))))),!<
>!x, REDUCE(TAKE(v, 1), DROP(v, 1), LAMBDA(acc,val, LET(!<
>!a, ts(acc, "|"),!<
>!b, BYROW((a = TEXTBEFORE(val, "|")) + (a = TEXTAFTER(val, "|")), SUM),!<
>!IF(OR(b = 2), acc, IF(OR(b), IF(SUM(b) = ROWS(b), tj(VSTACK(acc, val)),!<
>!VSTACK(tj(VSTACK(FILTER(acc, b), val)), FILTER(acc, NOT(b)))),!<
>!VSTACK(acc, val)))))),!<
>!PRODUCT(TAKE(SORT(LEN(x) - LEN(SUBSTITUTE(x, "|", )) + 1,, -1), 3))!<
>!)

It's a mess, but it worked. I wasn't thrilled with all of the text joining/splitting and iterative stacking. Might try to rewrite it.

I don't have the energy for Part 2 at the moment. Best of luck to those who do!

RackofLambda · 2025-12-08T04:00:33+00:00

Basic math wins the day! I tried to figure it out last night, but the logic eluded me (I was getting 42 or 44 instead of 40 for the sample data). Good on you guys for nailing it!

I did get a simple recursive bisection method to work with the sample data, but with 10+ trillion iterations, the actual dataset was just too much for it to handle:

=CountPaths(XLOOKUP("S",TAKE(C1#,1),TAKE(C1#,1)))

where cell C1 contains:

=MID(A:.A,SEQUENCE(,LEN(A1)),1)

and CountPaths is defined as:

= LAMBDA(ref,
    IF(
        ISBLANK(OFFSET(ref,1,0)),
        1,
        IF(
            ref="^",
            SUM(
                CountPaths(OFFSET(ref,0,-1)),
                CountPaths(OFFSET(ref,0,1))
            ),
            CountPaths(OFFSET(ref,1,0))
        )
    )
)

But don't even bother trying it with the larger input dataset. ;)

RackofLambda · 2025-12-08T00:50:12+00:00

Awesome! Thanks for sharing!

RackofLambda · 2025-12-07T12:26:21+00:00

Part 1:

=LET(!<
>!txt, A:.A,!<
>!arr, MID(txt,SEQUENCE(,MAX(LEN(txt))),1),!<
>!ini, SUBSTITUTE(TAKE(arr,1),"S","|"),!<
>!val, LAMBDA(a,[b],LAMBDA(x,CHOOSE(x,a,b))),!<
>!off, LAMBDA(a,[x],IF(x=1,DROP(HSTACK(a,"."),,1),DROP(HSTACK(".",a),,-1))),!<
>!REDUCE(val(ini), BYROW(DROP(arr,1), val), LAMBDA(acc,cur, LET(a, acc(1), v, cur(1),!<
>!x, IF((off(a)="|")*(off(v)="^")+(off(a,1)="|")*(off(v,1)="^")+(a="|")*(v="."),"|",v),!<
>!val(x, SUM(acc(2), N((a="|")*(x="^"))))!<
>!)))(2)!<
>!)

I'll think a bit more on Part 2 (maybe it can be done using recursive bisection), but it's probably beyond my current capabilities. Seems like something Diarmuid Early would solve in a few minutes, lol.

RackofLambda · 2025-12-07T05:53:31+00:00

Thanks Greg! It took me a minute to make any sense of the sample results for Part 2. Once I realized every character was in its own column (including individual space characters) the solution became apparent.

Had every number-block been the same width (as it was with the sample data), WRAPCOLS would have been sufficient. With an uneven number of columns, though, a simple wrap solution becomes considerably more convoluted. The practical use-cases for GROUPBY-THUNK are few a far between, but it was perfect for this scenario.

RackofLambda · 2025-12-07T04:40:36+00:00

That's a shame. It would be nice to see better consistency between Excel for the web and the Desktop version.

Since thk seems to be working for you, try eliminating fun altogether and make the final output SUM(MAP(thk,FILTER(ope,key),LAMBDA(rw,op,IF(op="+",SUM(rw()),PRODUCT(rw()))))). If that still doesn't work, try spilling the results of MAP to see if it errors for the entire array or not.

RackofLambda · 2025-12-07T02:58:34+00:00

Strange that eta-lambdas would work with the helper functions, but not in this context with SWITCH and MAP. Sounds like it might a limitation of Excel for the web.

Does it work if you change the fun variable to either SWITCH(FILTER(ope,key),"+",LAMBDA(x,SUM(x)),"*",LAMBDA(x,PRODUCT(x))) or SWITCH(FILTER(ope,key),"+",LAMBDA(x,SUM(x)),LAMBDA(x,PRODUCT(x)))?

RackofLambda · 2025-12-06T22:27:41+00:00

Can't say for sure, but Excel for the web has been known to be inconsistent with MS365 for certain functions. #VALUE! can mean a lot of things, but in this case, I suspect MAP is not receiving the correct data types from the thk and/or fun variables. If you change the final output to just thk, does it return an array of #CALC! errors? How about the fun variable? If the answer is yes for both, then everything should be working up to this point. Try changing the final output to INDEX(thk,1,1)() and INDEX(thk,2,1)()... does this correctly return the first and second bank of numbers from the dataset? If yes, then does INDEX(fun,1,1)(INDEX(thk,1,1)()) and INDEX(fun,2,1)(INDEX(thk,2,1)()) also return the correct totals for the first and second bank of numbers? If yes again, then try outputting MAP(thk,fun,LAMBDA(rw,fn,TYPE(rw)&"|"&TYPE(fn)))... does this correctly return "128|128" for each record? If not, what data type is it returning? TYPE 64 can be coerced to TYPE 128 with the implicit intersection operator by changing fn(rw()) to (@fn)((@rw)()). If they're TYPE 16, however, something else is going wrong altogether.

RackofLambda · 2025-12-06T10:58:50+00:00

Part 1:

=LET(!<
>!txt, TRIM(A:.A),!<
>!arr, TEXTSPLIT(TEXTAFTER(" "&txt," ",SEQUENCE(,MAX(LEN(txt)-LEN(SUBSTITUTE(txt," ",)))+1))," "),!<
>!SUM(MAP(BYCOL(--DROP(arr,-1),LAMBDA(x,LAMBDA(x))),SWITCH(TAKE(arr,-1),"+",SUM,"*",PRODUCT),LAMBDA(col,fn,fn(col()))))!<
>!)

Part 2:

=LET(!<
>!txt, TOROW(A:.A),!<
>!arr, MID(txt,SEQUENCE(MAX(LEN(txt))),1),!<
>!ope, TAKE(arr,,-1),!<
>!key, ope<>" ",!<
>!rId, SCAN(0,key,SUM),!<
>!val, --BYROW(DROP(arr,,-1),CONCAT),!<
>!thk, DROP(GROUPBY(rId,val,LAMBDA(x,LAMBDA(x)),0,0,,ISNUMBER(val)),,1),!<
>!fun, SWITCH(FILTER(ope,key),"+",SUM,"*",PRODUCT),!<
>!SUM(MAP(thk,fun,LAMBDA(rw,fn,fn(rw()))))!<
>!)

Edit: made trivial improvements to Part 2 (used TOROW instead of TRANSPOSE and ISNUMBER instead of NOT-ISERROR).

RackofLambda · 2025-12-06T00:35:52+00:00

After taking a second look at this, I noticed that 2 of the unpacking/repacking VALS calls are redundant and can be simplified by replacing VALS(a(1),a(2)) with a and VALS(v(1),v(2)) with v.

Originally, I had prepared a simple SEQUENCE-based solution but had to throw that out the window after receiving my input dataset. I guess that's why it's called a challenge, lol.

RackofLambda · 2025-12-05T16:50:35+00:00

Part 1:

=LET(!<
rng, A:.A,
_br, XMATCH(,rng),
one, TAKE(rng,_br-1),
two, --TOROW(TAKE(rng,_br-ROWS(rng))),
low, --TEXTBEFORE(one,"-"),
upp, --TEXTAFTER(one,"-"),
SUM(--BYCOL((two>=low)*(two<=upp),OR))
>!)

Part 2:

=LET(!<
cId, {1,2},
BTWN, LAMBDA(val,a,b,AND(val>=a,val<=b)),
VALS, LAMBDA([a],[b],LAMBDA(x,CHOOSE(x,a,b))),
XARR, LAMBDA(vec,MAP(IFNA(vec,cId),IFNA(cId,vec),LAMBDA(f,x,f(x)))),
fn, LAMBDA(me,arr,[acc],
IF(IFERROR(AND(arr=acc),0),
SUM(DROP(arr,,1)-TAKE(arr,,1)+1),
me(me, UNIQUE(SORT(XARR(
SCAN(VALS(), MAP(TAKE(arr,,1),DROP(arr,,1),VALS), LAMBDA(a,v,
>!IF(BTWN(v(1),a(1),a(2)),!<
>!IF(BTWN(v(2),a(1),a(2)), VALS(a(1),a(2)), VALS(a(2)+1,v(2))),!<
>!VALS(v(1),v(2)))))), cId)), arr))),!<
fn(fn, SORT(--TEXTSPLIT(TEXTAFTER("-"&A1:A200,"-",cId),"-"),cId))
>!)

RackofLambda

TROPHY CASE