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

     

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

 

Resources