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. 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: The actual replacement of the characters named 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 replace is provided by the LEFT function: 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
