LAMBDA count words

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:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

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:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0)

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:

=LAMBDA(text,LEN(TRIM(text))-LEN(SUBSTITUTE(text," ",""))+(LEN(TRIM(text))>0) (B5)

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.