CODEWITHSUNDEEP
excelrecursionlambdaexcel-lambda
draustine
draustine

Reputation: 35

Using an Excel Spill Range as data Source for a Recursive Lambda Function

I wrote an Excel LAMBDA function for extracting numbers from a string. This is the formula get.numbers=LAMBDA(text,ntext,position,size,VALUE(LET(x,LEN(text),n,position,IF(n>x,ntext,get.numbers(text,IF(ISNUMBER(VALUE(MID(text,n,size))),ntext&MID(text,n,size),ntext),n+1,size)))))

The formula works well when the text is from a single cell range eg A2, but if you try to use it on a spill range (A2#) it returns #NUM! error. I have another LAMBDA function that accepts a string and gets a character from a particular position in another string. It joins to character to the given string if the character is a number. I tried to use this function within the get.numbers function but it returned error.

My conclusions are

  1. Lambda function cannot be used within a recursive lambda function
  2. Recursive Lambda function cannot accept data from a spill range

Is there any way out of this?

Upvotes: 1

Views: 1183

Answers (2)

draustine
draustine

Reputation: 35

Following Axuary's answer above, I did further trials and was able to clean-up the formula such that the formula takes only one parameter (the string or a range containing strings). The final formula is

Call.GetNumbers.Array =LAMBDA(array,get.numbers.array(array,"",1,1))

It takes only one parameter (array) which could be a cell containing a string, or a spilled range
The main formula is:

get.numbers.array =LAMBDA(array,last,r,ln,LET(n,ROWS(array),q,SEQUENCE(n),lineresult,Call.Join.numbers(INDEX(array,r)),d,IF(r>n,last,get.numbers.array(array,Call.Join.numbers(INDEX(array,r)),r+1,ln+1)),IF(ln=q,lineresult,INDEX(d,q))))

The remaining formulae are:

Call.Join.numbers =LAMBDA(text,join.numbers(text,"",1))
join.numbers =LAMBDA(text,ntext,p,LET(l,LEN(text),VALUE(IF(p>l,ntext,join.numbers(text,ntext&get.If.Number(text,p),p+1)))))
get.if.Number =LAMBDA(text,x,IF(ISNUMBER(VALUE(MID(text,x,1))),MID(text,x,1),""))

The formula worked on range of 1 column by 95 rows

Upvotes: 0

Axuary
Axuary

Reputation: 1507

There seems to be some limits on the number of pending calculations you have have in a recursive lambda. So you have to be careful to do things in the right order. I got this to work for an array with up to 93 rows and one column. The call is =get.numbers.array(A2#, "", 1, 1, 1).

get.numbers.array =LAMBDA(textArray, ntext, position, size, x, 
  LET(s, ROWS(textArray), 
      q, SEQUENCE(s), 
      singleResult, get.numbers(INDEX(textArray,x), ntext, position, size), 
      d, IF(x=s, textArray, get.numbers.array(textArray, ntext, position, size, x+1)), 
      IF(x=q, singleResult, INDEX(d,q))))

It would require some more experimentation to get it to work for multiple dimensions.

EDIT 1: After some digging, I found the explanation for the limit here. It states

Furthermore, do note that the current operand stack limit in Excel is 1,024. This should be borne in mind together with calculation times, as the current recursion limit is set as 1,024 divided by (number of lambda parameters + 1).

Upvotes: 1

Related Questions