Forum Discussion

uziel9999's avatar
uziel9999
Copper Contributor
Jun 16, 2022
Solved

Lambda with offset does not work with a sequence

Why does this work?

 

=LAMBDA(a, a)({1,2})

 

 

but not this?

 

=LAMBDA(a, OFFSET(a,0,0))({1,2})

 

excel
  • Lorenzo's avatar
    Lorenzo
    Jun 18, 2022

    uziel9999 

    Regarding my original question, my goal was to take a string, split it to an array, then iterate over that array and apply it to some logic. For example, RemoveAll(str, "a b c") would split "a b c" to the array {"a","b","c"} and remove those letters from the string (str)

     

    Cf. 1st post from PeterBartholomew1 on this discussion this could be something like (assuming you want to use FILTERXML):

    =LAMBDA(string, chars, [delim],
    LET(
        Delim, IF(ISOMITTED(delim), "," ,delim),
        ToRemove, FILTERXML(
            TEXTJOIN(,,"<t><w>",SUBSTITUTE(chars,Delim,"</w><w>"),"</w></t>"),
            "//w"
        ),
        REDUCE(string, ToRemove,
            LAMBDA(str,chr, SUBSTITUTE(str,chr,""))
        )
    )
)

     

7 Replies

Sort By
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 17, 2022

    uziel9999 What is it that you expect from that OFFSET formula? OFFSET expect a cell reference in its first argument. Your LAMBA produces OFFSET({1,2},0,0) which is not accepted.

     

    Something like in the picture below would work, although that may not be what you want:

    • uziel9999's avatar
      uziel9999
      Copper Contributor
      Jun 17, 2022

      Riny_van_Eekelen thank you for your comments.  It is good feedback.

       

      I am trying to understand how to cycle through an array.  I have a lambda that breaks a string of words into individual elements.  Later in the formula that array of elements is cycled through and used.  How can it cycle through an array?  The purpose of the offset is to identify the tail and to pass it to the recursion.

       

      So, if the formula takes the string "a b c" and splits that into the array {"a","b","c"}, what is the pattern that uses "a" and passes {"b","c"} to itself through recursion?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 17, 2022

        uziel9999 

        If you mean to imitate out of the box (in Beta) TEXTSPLIT function that could be like

        splitText = LAMBDA(str, [sepPar],
    LET(
        sep, IF(ISOMITTED(sepPar), ",", sepPar),
        seps, MAKEARRAY(
            COLUMNS(sep),
            1,
            LAMBDA(r, c, INDEX(sep, r))
        ),
        sepPosition, MIN(IFERROR(SEARCH(seps, str), "")),
        IF(
            sepPosition = 0,
            str,
            expandArrayLeft(
                LEFT(str, sepPosition - 1),
                splitText(
                    RIGHT(str, LEN(str) - sepPosition),
                    sep
                )
            )
        )
    )
);

expandArrayLeft = LAMBDA(value, array,
    LET(
        columnIndex, SEQUENCE(, COLUMNS(array) + 1, 0),
        IF(
            columnIndex,
            INDEX(array, , columnIndex),
            value
        )
    )
);

Resources