excelexcel-formulalambda

Excel LAMBDA + spill

I've created this LAMBDA function and called it STR2ARR (note: yes, I know there are other functions in Excel that can do this, but I'm trying a concept that I want to use in a bigger LET/LAMBDA combination):

=LAMBDA(s_stock;[v_columns];IFERROR(VALUE(TEXTSPLIT(s_stock;"_";"x"));SEQUENCE(1;IF(ISOMITTED(v_columns);2;v_columns);0;0)))

So when I input this string: 45658_26x45779_5 , the function splits it into a 2x2 array.

Next, I have set up a small test block with some strings:

=SEQUENCE(4;1;10;5)

And next to it:

="400"&A1# & "_"& A1#

That gives me this test-range:

enter image description here

Question 1: I'd like to deduct a random number between 1 and 10 from the first column, but =A1#-RANDBETWEEN(1;10) gives me for every row the same "random number". Do I need to make a LET function to do this row by row?

Question 2 I've been trying to use my STR2ARR function based on that spilled range, but only get wrong outputs. I'd expect a 2 col x 4 row output where the contents is split up. My first formula will give the correct info for the first row, my second try gives the right amount of cells but the second column is the same as the first (would expect it to be different) and the last calculation throws a #CALC! error. Can anybody point me to the function I should use?

enter image description here

Solution

  • Here is what you can do to accomplish the desired output:

    • For 1st Question --> Use MAP() or BYROW() function, so that the RANDBETWEEN() works by row or by each element for the Spilled array:

    enter image description here

    =MAP(A1#, LAMBDA(x, x-RANDBETWEEN(1, 10)))

    Or,

    =BYROW(A1#, LAMBDA(x, x-RANDBETWEEN(1, 10)))

    You can also use RANDARRAY() function here:

    =A1#-RANDARRAY(ROWS(A1#), , 1, 10, 1)

    • For 2nd Question --> Encapsulate the B1# within a TEXTJOIN() function with a delimiter x

    enter image description here

    =STR2ARR(TEXTJOIN("x", , B1#))

    You can also use CONCAT() but it will require another additional function:

    =DROP(STR2ARR(CONCAT(B1#&"x")), -1)

    Similarly using ARRAYTOTEXT()

    =STR2ARR(SUBSTITUTE(ARRAYTOTEXT(B1#), ", ", "x"))

    Change the list separators per your regional settings.

    Caveat: Since the output is asked to produce within a cell hence using TEXTJOIN() there is character limitations i.e. if the string exceeds 32767 characters (cell limit), TEXTJOIN() returns the #VALUE! error. Here is the MSFT documentation!