 SOLVED

New Contributor

# 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})``

7 Replies

# Re: Lambda with offset does not work with a sequence

@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: # Re: Lambda with offset does not work with a sequence

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?

# Re: Lambda with offset does not work with a sequence

@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.

# Re: Lambda with offset does not work with a sequence

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
)
)
);``````

# Re: Lambda with offset does not work with a sequence

@Sergei Baklan 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
)
)``````

# Re: Lambda with offset does not work with a sequence

Perhaps I didn't catch something without sample file, but ArrayHead returns the same as

``````ArrayHead=
LAMBDA(array,[horizontal],
LET(
y, NOT(ISBLANK(horizontal)),
IF( ROWS(array)>1 ,
IF( y,
INDEX(array, SEQUENCE( ROWS(array)), 1),
INDEX(array, 1, SEQUENCE(, COLUMNS(array)))
),
INDEX(array, 1)
)
)
)``````
best response confirmed by uziel9999 (New Contributor)
Solution

# Re: Lambda with offset does not work with a sequence

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 @Peter Bartholomew 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,""))
)
)
)``````