How to Count Words in Excel (Fast & Easy Example)

Unlike Word, Microsoft Excel does not have a built-in tool for counting the number of words. However, this doesn’t mean Excel can’t. 😎

By combining several Excel functions to make complex formulas, anything is almost possible in Excel including how to count words. 🚀

This tutorial will show you how to count the total number of words and the number of specific words in a cell or a range of cells.

To start, download this sample Excel workbook we’ve created for you to work on.

Table of Contents

Excel functions you need to know first

To count words in Excel, you’re going to need the combination of the following Excel functions namely:

• LEN function
• TRIM function
• SUBSTITUTE function

Not to worry, you’ll learn how these functions work to be able to combine them into a single formula to count words in Excel.

Open your sample workbook and let’s see these functions in action. 💪

The LEN function

This function returns the number of characters in a text string, including the space characters.

To use this formula, type =LEN( and click the cell reference of the text string. In our example, it’s cell A2. Close this formula with a right parenthesis and press Enter on your keyboard.

Here, the formula counts 25 characters (including the spaces) of the text string in cell A2.

The TRIM function

In the example above, our text string has irregular spacing. We sometimes receive data in such a way that there can be extra spaces before the sentences and even between the words.

To remove all the extra spaces, use the TRIM function. ✂

This function removes all spaces from the text except for the single spaces between words of your text string.

Type =TRIM( and click the cell reference of the text string you want to trim. Close the formula with a right parenthesis and press Enter.

Excel now displays the text string in regular spacing, removing all the leading and trailing spaces.

To count the number of characters when trimmed, use the LEN function. The result decreases from 25 to 17.

The SUBSTITUTE function

From its name, the SUBSTITUTE function substitutes new_text for old_text in a text string. It basically replaces a specific text in a text string.

Here, we’re going to replace the spaces (“ “) between words with ““ using the SUBSTITUTE function,

Type the =SUBSTITUTE(, then click cell A2 for the cell reference containing the text string, add a comma symbol (,)

Then type “ “ for the old_text, add a comma symbol, and type ““ for the new_text. Close the formula with a right parenthesis.

Excel now displays the text string with no spaces in between the words.

If we count the number of characters for this text string, it decreases from 17 to 15.

Count the Total Number of Words in a cell

Now that we know how the LEN, TRIM, and SUBSTITUTE functions work, we need to combine them into a single formula to count the total number of words in a cell. 😊

First, we need to get the number of characters excluding the spaces at the beginning and end of the text string. We only need to count the spaces in between the words. So, type =LEN(TRIM(A2)) and add a minus symbol (-).

Then, we need the length of the string without spaces in between words, so type LEN(SUBSTITUTE(A2,” “,””))

This means that we subtract the length of the string without spaces in between words from the length of the string with spaces in between words.

Finally, we add 1.

Press Enter.

This is now the result.

The total number of words in a cell is equal to the number of spaces plus 1.

Now, it’s your turn. Use the above formula to count the words in a cell in the examples in our sample workbook. 👇

This should be the results.

Count specific words in a cell

You can also count specific words or text or substring in a cell. It uses the same Excel functions used above. We just need to tweak it a little bit.

It should follow the format below.

=(LEN(cell)-LEN(SUBSTITUTE(cell, “word”,””)))/LEN(“word”)

Using the sample text strings in our workbook, let’s calculate the number of “Excel” occurrences in cell A10.

Type the formula below (or copy this formula and paste it on your formula bar):

Then, press Enter.

The formula works by the SUBSTITUTE function subtracting the word “Excel” in the original text string first. Then the LEN function calculates the length of the string without the specific word “Excel”

The value is then subtracted from the length of the string of the original text =LEN(A10)

Partially, this results in 22 – 17 = 5

This is then divided by the number of characters of the specific word “Excel” or =LEN(“Excel”) which is equal to 5.

So, that’s 5 divided by 5, resulting in 1. ☝

There is 1 ”Excel” occurrence in the text string “Learning Excel is fun.” 😊

Count the Total number of words in a range

To count how many words there are in a specific range of cells , use the formula we’ve used earlier to calculate the total number of words in a cell and embed it within either the SUM or SUMPRODUCT function.

It should follow the formats below:

=SUM(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” ”,””))+1)

or

=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” ”,””))+1)

Let’s add the total number of words within the range A10:A12 in our workbook. Let’s use the SUM function for this.

Then, press Ctrl + Shift + Enter to complete the array SUM formula.

Using the above formula, the total number of words is equal to 15.

Count specific words in a range

In the same way, if you want to count the number of occurrences of a specific word within a range of cells, use the formula we’ve used earlier to count specific words in a cell and embed it with the SUM or SUMPRODUCT function.

It should follow these formats below:

=SUM((LEN(range)-LEN(SUBSTITUTE(range, “word”,””)))/LEN(“word”))

or

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range, “word”,””)))/LEN(“word”))

For our example in the workbook, let’s use the SUMPRODUCT.

To count all occurrences of the word “Excel” within the range A10:A12, use this formula: 👇

Press Enter.

There are 3 number of occurrences of the word “Excel” within the range A10:A12.

That’s It—Now What?

Great job! 👏

Even without a built-in word count tool, MS Excel can still count words in a cell or a range of cells. That’s because of Excel’s powerful functions.

When you know what functions to use and combine them into one formula, you can do almost anything in Excel. 🚀

To start, explore these related Excel tutorials we’ve handpicked for your next read:

• How to Count Cells in Excel (COUNT Function Guide)
• How to Count Cells With Specific Characters or Text Using a Formula
• How to Count Unique Values in Excel (and Distinct Values)
• How to Use COUNTIF in Excel: Step-by-Step (+COUNTIFS)
• How to Count Not Blank Cells (Excel COUNTIF Function)