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

Question

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?

and BTW with REDUCE we can shorten the whole thing and not need the name range any more: `=REDUCE(A1,{"AA","AB","AC","AD"},LAMBDA(a,b,SUBSTITUTE(a,b,"")))` — Scott Craner, Jul 13 '22 at 13:44
@ScottCraner The `CONCAT` doesn't perform a stepwise function over the replacements. The reduce function however works (and with ranges) and is pretty much what I need! Add as an answer and I can accept — Tragamor, Jul 13 '22 at 13:50

score 4 · Accepted Answer · answered Jul 13 '22 at 13:54

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

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.

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

1 Answers1