LAMBDA count words

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.

April 14, 2025 · 1 min · 56 words · Ryan Watts

Table of Contents

Explanation

TheLAMBDA functioncan be used to create reusable, custom functions in Excel without VBA or macros.

Here is the formula in action below.

You canread a detailed explanation here.

Standard Excel formula for counting words

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching.

LEN will also count characters in numbers, but number formatting is not included.

Standard Excel formula for counting words

Standard Excel formula for counting words

Standard Excel formula for counting words

Standard Excel formula for counting words

Unnamed LAMBDA formula for counting words

Custom CountWords function now available

Custom CountWords function now available

Excel formula: Count total words in a cell

Excel formula: LAMBDA replace characters recursive

Excel LAMBDA function

Excel SUBSTITUTE function

Excel TRIM function

Excel LEN function

Standard Excel formula for counting words

Unnamed LAMBDA formula for counting words

Custom CountWords function now available

Custom CountWords function now available