Discover two techniques for counting words in Excel.

1. TEXTSPLIT function: If you work with Excel 365, this is the easiest technique.
2. Build a trick solution by counting the number of spaces between each word 😉

Method with TEXSPLIT function 👍

The easiest method requires Excel 365. We will use only one function: TEXTSPLIT.

Counting words with this function is simple. TEXTSPLIT divides your text using a delimiter. For example, this formula will split the text into several cells.

Notice that each word appears in a separate cell. For the first sentence, we have four cells. Use the COUNTA function to count these cells.

=COUNTA(TEXTSPLIT(B2," "))

Technique with more formulas

If you lack TEXTSPLIT, here is a second method requiring extra steps.

Step 1: Count all characters (including spaces)

First, count all characters in the cell.

1. Trim extra spaces using the TRIM function.
2. Use the LEN function for counting the number of characters in the cell.

=LEN(TRIM(B2))

Step 2: Remove all spaces

Now, use the SUBSTITUTE function to remove all spaces in your cell.

1. Select your original cell
2. Indicate the space character " " (double-quote, space, double-quote)
3. Replace by nothing "" (double-quote, double-quote)

Step 3: Count the number of remaining characters

Count the characters left after removing the spaces with the LEN function.

Step 4: Count the number of words in Excel

Here is the trick.

• Subtract the two counts. This will return the number of words in your cell
• Then, add one to get the accurate word count.

Integrate these steps into a LAMBDA function

Excel 365 or Excel Online users can integrate these steps into one formula. Use the LAMBDA function. This method ensures end-users don't write the steps incorrectly. Integrate them using the LET function.

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

Save this formula as a named range from Formulas > Define a name.

Name this formula as COUNTWORDS. Then, paste the formula into the Reference TextBox.

Then, use this custom formula in a cell, adding your text as the argument.

Related articles

• Splitting a text based on a pattern in Excel
• Extract text with AI in Excel with FlashFill
• TEXTBEFORE and TEXTAFTER
• Split Text on a Delimiter without Formula
• Split your text on a delimiter – TEXTSPLIT Function
• Create an alphabet series in Excel 🔠