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

I came across a solution that fit my needs in this reddit post and thought I would share it here, since it was well explained. All credit to reddit user wjhladik.

The formula to copy and paste in excel, tweak as necessary:

=LET(phrase,A1,

text,A4:A6,

replwith,B4:B6,

REDUCE(phrase,SEQUENCE(ROWS(text)),LAMBDA(newphrase,next,SUBSTITUTE(newphrase,INDEX(text,next),INDEX(replwith,next)))))

For my purposes I used filter produced arrays instead of ranges, and it worked perfectly. A very specific use case, but sharing in case this helps someone else:

=LET(phrase,[@Household],

text,FILTER(Table3[Full Combined Name], Table3[Exposure Address]=(TEXTJOIN(CHAR(10),TRUE,FILTER(Table2[Residential Exposure Address], Table2[Combined Name]=[@[PNN Name]]))), [@Household]),

replwith, FILTER(Table3[Full Combined Name and Batch], Table3[Exposure Address]=(TEXTJOIN(CHAR(10),TRUE,FILTER(Table2[Residential Exposure Address], Table2[Combined Name]=[@[PNN Name]]))), [@Household]),

REDUCE(phrase,SEQUENCE(ROWS(text)),LAMBDA(newphrase,next,SUBSTITUTE(newphrase,INDEX(text,next),INDEX(replwith,next)))))

• Phrase is the starting text string.

• Text is an array of words (case sensitive) you want replaced in Phrase.

• replwith is an equal array of text strings you want to to use as the substitution text.

• REDUCE starts with phrase and then iterates n times where n is the length of the text array. Each time it substitutes one element of the text array with the corresponding element of the replwith array. The result after n iterations is newphrase.

This approach has some downsides which are mentioned in the reddit thread, but it worked well for me.

The downsides as brought up by reddit user PaulieThePolarBear:

1. SUBSTITUTE is case sensitive, so a user would need to consider the cases they require in your lookup table.

2. Your approach is replacing text strings not words. So an input phrase of "My telephone number is one." becomes "My telephred number is red." as the text string "one" appears in the word "telephone". This may be the required output for a user, so just a note of caution if someone uses this approach.

3. Using an iterative approach on the table may lead to "double" changes. If the output word for your "one" row in your lookup table is the made up word "twop", my phrase of "My telephone number is one." becomes "My telephwhitep number is whitep.". In this instance, the first iteration changed "one" to "twop". The second iteration changed "two" to "white".

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

Answer 3

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.