LAMBDA replace characters recursive

The LAMBDA function can be used to create custom, reusable functions in Excel. This example illustrates a feature called recursion, in which a function calls itself. Recursion can be used to create elegant, compact, non-redundant code. However, one disadvantage to recursive LAMBDA functions as present is they are not easy to test. This is because they can't be debugged directly on the worksheet, since a generic (unnamed) LAMBDA does not yet have a name and therefore can't call itself.

When creating a recursive LAMBDA formula a key consideration is how the formula will "exit" the loop it performs by calling itself. One common approach is to deplete an input parameter each time the formula calls itself, then check if the input is fully depleted before each call, and exit if so. In this way, the input parameter acts like a counter, counting down to zero.

This is the approach taken in this formula – the chars parameter acts like a counter and one character is removed each time the formula calls itself. Before the recursive call, the IF function is used to check if chars is empty. If so, the formula returns the current value for str as a final result and exits. If not, the formula calls itself:

=LAMBDA(str,chars,sub,
  IF(chars="",str, // test and exit if needed
    REPLACECHARS( // recurse
      SUBSTITUTE(str,LEFT(chars),sub),
      MID(chars,2,LEN(chars)-1),
      sub
    )
  )
)

The actual replacement of the characters named in chars with the value of sub is handled by the SUBSTITUTE function here:

SUBSTITUTE(str,LEFT(chars),sub)

SUBSTITUTE can only perform one replacement at a time, which is why this formula is recursive. The parameter str is originally the text string provided to the function for character replacement, but note this value is potentially changing each time the function calls itself. The character to replace is provided by the LEFT function:

LEFT(chars) // get first character of chars

The value to use for replacement comes from sub. The trick to understanding the formula is to see that the result from SUBSTITUTE is used directly to call the ReplaceChars function, and this result becomes the "next" str parameter. In addition, each time ReplaceChars is called, the string chars is depleted by one character with the MID function, and the result from MID becomes the next value for chars used in the call to ReplaceChars.

MID(chars,2,LEN(chars)-1) // remove first character of chars

Once chars has been fully depleted (i.e. becomes an empty string), the  logical test inside the IF function returns TRUE, and the formula returns the current value of str as a final result.

Extending the formula

Custom LAMBDA functions behave like other functions, so can easily extend functionality by nesting. For example, you could strip punctuation and replace with a space character (" "), then clean things up after by nesting ReplaceChars inside of the TRIM function like this:

=TRIM(ReplaceChars(B5,"!@#$%^&*()[]<>-?.,"," "))

The TRIM function will return leading and trailing spaces, and normalize space between words to one space. This will avoid the problem of words being combined when they are separated by punctuation only.

You can find more general information about the LAMBDA function here.