As we require three parameters for the three cell references used by the SUBSTITUTE function, we will use the following:
- T = the text being examined
- B = the “Before” text
- A = the “After” text
We will select an empty cell and write the following start to the LAMBDA formula.
=LAMBDA(T, B, A,
The next part is to test to see if the “Before” cell being examined is empty. This will be performed using an IF function. We test to see if the “B” parameter is empty, then we exit the LAMBDA by outputting the current state of “T”.
IF(B = “”, T
If “B” is not empty, then we need to invoke recursion.
This is where things get tricky.
As we will ultimately name this LAMBDA “MegaReplace”, we will make a call to “MegaReplace” as the FALSE action of the IF function.
IF(B = “”, T, MegaReplace
You must be able to imagine the result; you can’t really test it here.
Because the “MegaReplace” function requires three parameters, the first parameter will be the SUBSTITUTE formula created earlier.
SUBSTITUTE(T, B, A)
This will act as our “T” in the “MegaReplace” function.
Next, the “B” in the “MegaReplace” must loop through the list of “Before” entries. This can be accomplished using an OFFSET function.
Remember, LAMBDAs don’t use cell references. We use parameters. The cell references are in the parameters. The “B” refers to the current “Before” cell location.
Continuing with the OFFSET function:
OFFSET(B, 1, 0)
This moves 1 row down and 0 columns left/right.
The LAMBDA is now looking as follows.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0)
If we move down 1 row in the “Before” column, we must also move down 1 row in the “After” column. We need to perform another OFFSET with nearly the same logic, except we use “A” instead of “B”.
OFFSET(A, 1, 0)
The revised formula now looks like so.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0)
The final steps are to complete all the parentheticals by placing the perfect number of closed parentheses at the end of the formula.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) )
When we press ENTER we receive a #CALC! error. This is because the formula is calling a “MegaReplace” function that doesn’t yet exist.
This takes us to the third and final step, the naming of the LAMBDA.