3 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Iterative Lambda returning spill array when output should just be single string.

Howdy All,

I am attempting to make a lambda formula that will loop through an array, successively REGEXREPLACE a string with the first part of the array, drop that first part to shorten the array and after looping though it all, return the string with everything replaced. This is all a small component part of a much larger lambda I plan to make. The array input should be able to be any array, but for the purposes of helping, presume it is a set of cells in a table.

Since the input is a single string, and I'm only processing one specific thing each iteration, the output should be a single non-spilling string. However, my lambda is somehow outputting a spill, duplicating the end result into two cells, and for the life of me I cannot figure out why. I've tried quite a few variations of my array, and eventually I've walked through it step by step as if it weren't an array. I'll post the lambda below, post the test pieces I'm using, report the output, then I'll show step by step what I see if I do it piecemeal outside of an iterative array. If anyone can provide a clue on why it is behaving this way and a recommended direction to take, it would be much appreciated.

The custom iterative lambda:

=LAMBDA(text,array, IF(array="",text, LoopLambdaV2(REGEXREPLACE(text,INDEX(array,,1),""), IF(ISERR(DROP(array,,1)),"",DROP(array,,1))))) 

I'm testing it with cells A2:C2, filled with a, b, and c, respectively.

A1 has the text of 'Testing removing a, then b, then maybe c.'

Pick any cell to put in LoopLambdaV2(A1,A2:C2) . I put it in A4. The output I am getting is

A4: Testing removing , then , then mye . B4: Testing removing , then , then mye . C4: #N/A. 

Now, besides logically working my way through it saying I should have a single output, I tested each bit in the following way:

In nearby cells A6 and B6 respectively, I put

=REGEXREPLACE(A1,INDEX(A2:C2,,1),"") =DROP(A2:C2,,1) 

Outputs were:

A6: Testing removing , then b, then mybe c.

and spilling:

B6: b
C6: c

That mimics the first go-round. I then put in the following to mimic the second go-round:

A7: =REGEXREPLACE(A6,INDEX(B6#,,1),"")
B7: =DROP(B6#,,1)

Outputs were:

A7: Testing removing , then , then mye c.
B7: c

I did the same pattern in cells A8 and B8 to mimic third go-round:
A8: =REGEXREPLACE(A7,INDEX(B7,,1),"")
B8: =DROP(B7,,1)

Outputs:

A8: Testing removing , then , then mye .
B8: #CALC!

As per the last part of my Lambda, the CALC error should be turned empty, and I confirmed it in cell B9 via: =IF(ISERR(B8),"","other").

The final (4th) go-round of the lambda should see that the array is now "", and should output the last thing in text, which as demonstrated above should be a single string of "Testing removing , then , then mye ."

Please, can anyone provide me some guidance? I've been futzing with this for 2 days and it's driving me bonkers.

Respectfully,

NiptheZephyr

submitted by /u/NiptheZephyr
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#financial modeling with spreadsheets
#natural language processing for spreadsheets
#Excel alternatives for data analysis
#no-code spreadsheet solutions
#generative AI for data analysis
#rows.com
#AI formula generation techniques
#Excel compatibility
#large dataset processing
#lambda
#REGEXREPLACE
#output
#array
#input
#string
#spill
#text
#single string
#cell
#function