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,""))
        )
    )
)

     

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
    Diamond Contributor
    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
        )
    )
);
    • uziel9999's avatar
      uziel9999
      Copper Contributor
      Jun 17, 2022

      SergeiBaklan Thank you for your response.

       

      First, you might consider using FilterXML instead of the logic you posted.  I think the results are the same with much less code, but maybe I am missing something.

      =LAMBDA(text,delim,FILTERXML("<x><y>" & SUBSTITUTE(text, delim,"</y><y>") & " </y></x>", "//y"))

      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).  The problem that arises is that offset does not work, so how can the tail be sent through a recursive lambda to the next iteration.

       

      My solution is to append a sequence to the array, filter on and then remove that column.  I do not think it is particularly elegant, but it does work.  Thoughts?

      ArrayHead

LAMBDA(arr,[horizontal],
    LET(
        x,  AND(OR(ISOMITTED(horizontal),horizontal=0),ROWS(arr)>1),
        a,  IF(x,arr,TRANSPOSE(arr)),
        b,  LET(
                aa, ROWS(a),
                bb, COLUMNS(a)+1,
                cc, SEQUENCE(aa),
                dd, MAKEARRAY(
                        aa,
                        bb,
                        LAMBDA(
                            x,
                            y,
                            IF(
                                y=1,
                                INDEX(cc,x,y),
                                INDEX(a,x,y-1)
                            )
                        )
                    ),
                ee, MAKEARRAY(
                        1,
                        bb,
                        LAMBDA(
                            r,
                            c,
                            IF(c=1,0,1)
                        )
                    ),
                ff, FILTER(FILTER(dd,INDEX(dd,,1)=1),ee),
                IF(x,ff,TRANSPOSE(ff))
            ),
        b
    )
)
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        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,""))
        )
    )
)

         

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

    uziel9999 sorry, I know a bit about LAMBDA but have no clue what you are talking about. Perhaps you can share a file that demonstrates what you are trying to achieve and what the problem is.

Resources