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?

Marleen

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.

Marleen

Very useful but the only problem is going to be when you write for someone else and they aren't using Office365 – Guyton

@TobyPython, that's a problem with all version-based functions unfortunately. – Marleen

what's the meaning of @del - why the @ symbol? – Evacuation

Also I posted a question on recursive Lambdas on code review, maybe post yours there if you are looking for another way of doing it - for example taking a range and using Offset to iterate over it may be a cleaner approach, I'm not sure – Evacuation

@Greedo, the thing is I knew about OFFSET() but since that is volatile I don't know if it would make the LAMBDA() volatile too. The @ makes that we do not use an entire array but make sure we take the first element from our del array (or string). Without it, the LAMBDA() messes up. Hope that helps. Thanks for your interest in the function =) – Marleen

Answer 2

Well, best answer has already been declared, apparently, but, for what its worth, this doesn't handle consecutive delimitters of the same type very well,

Say, for instance, say I have "¬¬" and I wish to replace only such instances with a single "¬"; then applying the SubAll function will in fact remove every occurrence of "¬" - not just when these occur in adjacent 'pairs':

(The converse - adding an extra "¬", simply add an extra one).

Far more efficient/savvy (in my biased opinion), reduce - requires Office 365. btw.

a,

=REDUCE(B2,SEQUENCE(5),LAMBDA(a_,b_,SUBSTITUTE(a_,"¬¬","¬")))

Boole