Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
Re: Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
***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`.
[](https://i.sstatic.net/pMgXT.png)
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:
[](https://i.sstatic.net/b5bBP.png)
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,
*(Réponse tronquée)*
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`.
[](https://i.sstatic.net/pMgXT.png)
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:
[](https://i.sstatic.net/b5bBP.png)
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,
*(Réponse tronquée)*