Exceljet

Quick, clean, and to the point

LAMBDA count words

Excel formula: LAMBDA count words
Summary 

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.

Explanation 

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: 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 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 Manage Manager will propagate to all instances of the function in the worksheet. 

LAMBDA is a beta function available in Excel 365 only.
Author 
Dave Bruns

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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.