Search code examples
excelexcel-formulaexcel-lambda

Create an array from hard-coded values for use in a LAMBDA function

I have recently created a LAMBDA function for recursive substitutions of substrings which works when using a range:

LAMBDA named ReplaceArray

=LAMBDA(str, list, sub, IF(ROWS(list)=1, SUBSTITUTE(str,list,sub), ReplaceArray(SUBSTITUTE(str,INDEX(list,1),sub),OFFSET(list,1,0,ROWS(list)-1),"")))

So for example with a range of cells:

A
1 ABBAAABACDBDADCD
2 AA
3 AB
4 AC
5 AD 
=ReplaceArray(A1,A2:A5,"")

Gives cell value "BDBDCD"

However, when I try and use an array of values, I instead generate a SPILL range where in this case I have four values where each of the hard-coded values have been replaced in turn.

=ReplaceArray(A1,{"AA","AB","AC","AD"},"")

Interesting, but not what I am after.

I have tried using TRANSPOSE on the list to no avail.

Does anyone know how would I go about being able to use a hardcoded set of strings?

Solution

  • When LAMBDA was first introduced the best way to use it was in the Name Manager. Since then there are a myriad of Helper formula that allow us to use it outside the Name Manager and make the formula simpler and easier to understand.

    For example in this case we use REDUCE() which steps through an array and returns a single answer.

    =REDUCE(A1,{"AA","AB","AC","AD"},LAMBDA(a,b,SUBSTITUTE(a,b,"")))

    enter image description here

    This will work with Arrays or Ranges.

    There are others like SCAN,BYROW, BYCOLUMN, and others that all help control how LAMBDA iterates and returns results.