Where text represents a text string. As the formula is copied down, CountWords returns the count of words in column B. Note: the LAMBDA function is available through the beta channel of Excel 365 only. This formula uses three built-in functions: SUBSTITUTE, TRIM, and LEN. Here is the formula in action below. You can read a detailed explanation here.
The formula only requires one input parameter, the text in cell B5, so the LAMBDA function will have two arguments, the text from a cell, and the calculation to perform. Here is the formula converted to LAMBDA: Notice text appears as the first argument, and the word count calculation is the second argument. To test the LAMBDA version of the formula, use the syntax below: Note the reference to B5 in parentheses at the end is used for the text argument.
The results from the generic LAMBDA formula are the same as the original formula, so the next step is to name the formula “CountWords” in the Name Manager. Once the name has been created, CountWords function can now be used in the workbook:
In the screen below, we’ve replaced the generic (unnamed) LAMBDA formula with the named LAMBDA version, and entered B5 for text.
Like all custom LAMBDA function, any changes to the formula defined in the Name Manager will propagate to all instances of the function in the worksheet.
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.
