The LAMBDA function can be used to create a custom function to count total words in a cell. In the example shown, cell C5 contains the custom function "CountWords", based on this formula:

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

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:


This formula uses three built-in functions: SUBSTITUTETRIM, and LEN. Here is the formula in action below. You can read a detailed explanation here.

Standard Excel formula for counting words

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.

Unnamed LAMBDA formula for counting words

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:

Custom CountWords function now available

In the screen below, we've replaced the generic (unnamed) LAMBDA formula with the named LAMBDA version, and entered B5 for text.

Custom CountWords function now available

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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.