Exceljet

Quick, clean, and to the point

LAMBDA replace characters recursive

Excel formula: LAMBDA replace characters recursive
Generic formula 
=LAMBDA(str,chars,sub,
  IF(chars="",str,
    ReplaceChars(
      SUBSTITUTE(str,LEFT(chars),sub),
      MID(chars,2,LEN(chars)-1),
      sub
    )
  )
)
Summary 

The LAMBDA function can be used to create a custom function to replace characters. In the example shown, cell C5 contains the custom LAMBDA function "ReplaceChars":

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

The ReplaceChars function is designed to accept three input parameters:  str is the text to perform replacements on, chars is a text string containing the characters to replace, and sub is is the character (or characters) to substitute when a character is found. In the example shown, ReplaceChars is configured to find the punctuation characters seen in the second argument (chars), and replace them with an empty string (""). This custom LAMBDA formula illustrates a feature called recursion, in which a function calls itself.

Note: the LAMBDA function is available through the beta channel of Excel 365 only.

Explanation 

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, once 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 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 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 replaced 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 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.

LAMBDA is a beta function available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.