Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()

Question

At risk of being off-topic I decided to do a little Q&A as I'm pretty excited about a new function MS is introducing to Excel365; the LAMBDA() function. If the general opinion is such that this is off-topic, please let me know and I can take down the Q&A.

The LAMBDA() function is basically your way in Excel itself to create your own function. You then can go ahead and call this function throughout your entire workbook. But the absolute great thing (IMHO) about it is that it is able to call itself within the function, thus being recursive!

We all know the tedious nested SUBSTITUTE() functions if one has to swap multiple characters, or clear a string from certain characters and even whole words. So the question is: How do we avoid that and use LAMBDA() to our advantage?

Answer 1

EDIT 22-3-2022:

As per the new functionality, one can choose to opt for:

=CONCAT(TEXTSPLIT(A1,{"+","#","%","*","(",")","!"}))

I'll keep the original answer using LAMBDA() intact below:

---

Original Answer:

So let's create an example of a string that needs cleaning; a+b#c%d*e(f)g!h.

Formula in B1:

=SUBALL(A1,"+#%*()!","")

Where SUBALL() is the name of our LAMBDA() function I created through the "name manager" menu and reads as follows:

=LAMBDA(str,chrs,sub,IF(chrs="",str,SUBALL(SUBSTITUTE(str,LEFT(chrs),sub),RIGHT(chrs,LEN(chrs)-1),"")))

Core of this formula are the 3 variables:

• str - A reference to the string to be cleaned.
• chrs - A string of characters to be substituted.
• sub - What do we want our characters to be replaced with?

The 4th parameter is a nested IF(). Because of the recursive calls we need a way out of an otherwise infinite loop. Therefor we test if chrs="". If TRUE we return the final string with all substituted characters. If FALSE we call the function again. The great thing here is we can alter all variables! This is important because we can thus SUBSTITUTE() the leftmost character and we can cut that same character of the string of replacements.

We could also take it up a notch and replace element from an array. For example:

The formula in B1:

=SUBALL(A1,{"STR1","STR2","STR3"},"-")

Note, you can also hardcode a single value or reference a single cell (or any vertical range for that matter). Obviously this will impact the way we handle recursion. There may be a prettier way, but I came up with:

=LAMBDA(str,del,sub,IF(COUNTA(del)=1,SUBSTITUTE(str,@del,sub),SUBALL(SUBSTITUTE(str,@del,sub),INDEX(del,SEQUENCE(COUNTA(del)-1,,2)),sub)))

The core of the function is still the same, but as mentioned we have now used an array. So our IF() will no longer check for empty value, but if there is only a single element in our array. If so, it will go do a single SUBSTITUTE(), but if not, then it will recursively call SUBALL() untill we have sliced enough values from our array through INDEX() so all values are substituted.

There you have it, a recursive function that you can now call throughout your entire workbook. Pretty cool.