The StripTrailingChars function is designed to accept two input parameters: str is the text from which to remove trailing characters, and char is the trailing character to remove. In the example shown, StripTrailingChars is configured to remove the characters seen in column C from the text seen in column B. These characters will only be removed when they are “trailing”, i.e. when they appear at the end of the 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. In this example, before the recursive call happens, the IF function is used to check the last character of the input text string (str). If the last character is not equal to the target character (char) the function exits and returns the current value of str. Otherwise, the formula calls itself: The actual removal of the trailing character is handled by the MID function and LEN function: The MID function returns the value of str without the last character. MID strips one character at a time, which is why this formula is recursive. The result is given to the StripTrailingChars function, along with the original value for char. When all the target trailing characters have been removed, the function returns the current value for str. 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.
