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.
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:
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:
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 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 Manage Manager will propagate to all instances of the function in the worksheet.
LAMBDA is a beta function available in Excel 365 only.
Excel doesn't have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula...
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 SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
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.