The LAMBDA function can be used to create custom, reusable functions. In the example shown, cell D5 contains the custom LAMBDA function "StripTrailingChars":
The StripTrailingChars function is designed to accept two input parameters: str is the text from which to remove trailing characters, and char is a 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 the are "trailing", i.e. when the appear at the end of the string. This custom LAMBDA formula illustrates a feature called recursion, in which a function calls itself.
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. This example is primarily proof of concept, to show a very simple recursive LAMBDA function.
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 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:
=LAMBDA(str,char,IF(RIGHT(str)<>char,str,// test and exit if needed
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.
LAMBDA is a beta function available in Excel 365 only.
The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In this example, the base formula is...
In this example, the goal is to remove non-numeric characters from a text string with a formula. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW...
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...
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
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.