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

## Method with TEXSPLIT 👍

**This is the EASIEST technique** but you must work with Excel 365. Because here, we will use a single function **TEXTSPLIT.****Counting 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, with this formula, TEXTSPLIT will write the text in as many cells as necessary.

You can see that all the words are displayed in as many cells as needed. For the first sentence, we have four cells. So, we need to count the number of cells with the COUNTA function. Simple 😉

=COUNTA(TEXTSPLIT(B3," "))

## Technique with more formulas

If you don't have TEXTSPLIT, **here is the second technique, which requires more manipulation**.

### 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, the LEN function is used. But that's not all. We also need to ensure 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.

### Step 4: Count the number of words in Excel

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

### 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 make 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.

Rick Rothstein

13/05/2022 @ 23:34

In your Lambda, you should replace the number 1 at the end of it with this so that the function returns the number 0 for an empty cell instead of returning the number 1...

(LEN(Text) > 0)