This article will show you 2 techniques to count the words in an Excel cell.

Method with TEXSPLIT

TEXTSPLIT is a new function for Excel 365. Count the words in a cell with this function is very easy

As the name suggests, TEXTSPLIT splits your text according to a delimiter. For instance, in this example, TEXTSPLIT will write the text in as many cells as necessary with this formula

=TEXTSPLIT(B2," ")

And when you write this function inside the COUNTA function, you find the number of words in your cell.

=COUNTA(TEXTSPLIT(B3," "))

Technique with more formulas

Step 1: Count all characters (including the spaces)

The starting point for counting the number of words is to count all the characters in the cell. For this, there is the LEN function.

But that's not all, we also need to ensure that there are no "parasitic" spaces at the beginning or the end of the string. And for that, there is the TRIM function.

=LEN(TRIM(B2))

Step 2: Remove all spaces

The trick is to remove all the spaces and count the number of letters again 😉

We are therefore going to remove all the spaces present using the SUBSTITUTE function. The character to replace is the space " " and we replace it with nothing, i.e. "" (2 quotes placed side by side)

=SUBSTITUTE(B2," ","")

Step 3: Count the number of remaining characters

This time we will count the number of characters remaining after removing the spaces.

=LEN(D2)

Step 4: Find the number of words

All that remains is to make the difference between the 2 containing cells without forgetting to add 1 unit.

=C2-E2+1

Integrate these steps into a LAMBDA function

For Excel 365 or Excel Online users, you can integrate all the steps into a single formula and call it by a custom name. To do that, you must use the LAMBDA function.

With this technique, you secure your result because you are sure that your end-users won't generate a mistake in the writing of one of the steps 😉

Writing the LAMBDA function is quite complex. The 4 steps are integrated into the LET function, it is the only way to proceed.

=LAMBDA(text,LET(TrimSpace,TRIM(text),LengthText,LEN(TrimSpace),DelSpace,SUBSTITUTE(TrimSpace," ",""),LengthText-LEN(DelSpace)+1))

This formula should be in a named range from the menu Formulas > Define a name.

Then, you give a name for this formula; like COUNTWORDS, and you paste the previous formula into the Reference TextBox

And then, you just have to write this custom formula in a cell with the text as an argument.