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
– Scott Craner, Commented Jul 13, 2022 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
– Tragamor, Commented Jul 13, 2022 at 13:50

Scott Craner · Accepted Answer · 2022-07-13 13:54:57Z

4

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.

answered Jul 13, 2022 at 13:54

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related