LAMBDA replace characters recursive
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":
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.
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:
The actual replacement of the characters named in in chars with the value of sub is handled by the SUBSTITUTE function here:
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.
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:
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.