Counting words in Excel is something that can be quite useful in some scenarios. While an Office product like Word counts words automatically, you have to use a formula in Excel to accomplish this.

Creating the formula requires you to organize and think about what it will actually do, i.e. count the words. With the help of a simple Excel function, you can count characters easily.

Counting Characters in Excel

To count the characters, you can use the LEN function. The LEN function returns a number as the number of characters in the given cell or string. It counts symbols, punctuation marks, spaces, and numbers, as well as letters. To count the characters in a single cell:

  1. Select the cell you want to display the output in (Cell E2 in this example).
  2. In the formula bar, enter the formula below and press Enter:
             =LEN(A1)
        
  3. The formula cell will now show the number of characters in cell A1. Note that spaces and punctuation marks all count as characters as well.
The LEN function in Excel counts the characters

If you try and feed a range of cells to the LEN function, for instance, A1:A3, you'll notice that it will only count the characters in cell A1. To get the number of characters in a range with the LEN function, you'll have to couple it with the SUM function.

In this example, we have three sentences in cells A1 to A3 and want to display the count of characters in cell E2.

Couple with the SUM function, the LEN function counts the characters in a range of cells
  1. Select cell E2 and in the formula bar, enter the formula below:
            =SUM(LEN(A1),LEN(A2),LEN(A3))
        
    This will count the characters for the three cells separately and then sum them.
  2. Press Enter.
  3. The sum count of characters in the three cells will now appear in cell E2.

Counting Words in Excel

When you're talking about counting words in a text, you are, in fact, trying to count the spaces between the words. Between two words, comes a single space. Therefore, if you have four spaces in a sentence, you have five words. To calculate the number of words, you'll have to count the spaces and sum the result with one.

If you have a single word in your cell, you won't have any spaces. Zero spaces, summed with one, will tell you that you have one word in your cell.

Related: Microsoft Excel Autofill Tricks to Build Spreadsheets Faster

What Should the Formula Consider?

This method is, of course, not absolute. For instance, if you have typed multiple spaces between words, then all of those spaces will be mistakenly counted as words, resulting in more words than there are.

Another hole would be when a cell contains nothing but space or spaces. In that case, the word count result should be zero though counting the spaces will yield another, higher, result.

With this in mind, in order to count the words in Excel, you have to use a formula that accounts for all the possible holes. Luckily, using the basic Excel functions and combining them into a compound formula allows you to achieve this.

The Word Count Formula

The formula is going to be a compound formula utilizing several basic Excel functions. The formula below will do the word count for you.

        =SUM(IF(LEN(TRIM(range))=0,0,LEN(TRIM(range))-LEN(SUBSTITUTE(range," ",""))+1))
    

Let's see what each part of the formula actually does.

1. TRIM Extra Spaces

The first thing we need to get out of the way is cells that contain nothing or only contain spaces. One useful function here will be the TRIM function.

The TRIM function basically removes all extra spaces in a cell and keeps only the single space between words. This gives the formula a neat sentence to work with and prevents the formula from counting the extra spaces as words. If you want to learn more about the TRIM function and see it in action, read our article on how to use the TRIM function in Excel.

2. Counting the Spaces and the Letters

Counting the characters in a range is possible with the LEN function. However, since counting words is to count the spaces, you need to somehow count the spaces. Spaces are counted with the LEN function, however, so are all other characters.

Related: Microsoft Excel Formulas That Are Extremely Useful

However, getting the number of spaces in a cell is still possible with the LEN function. Simply count all the characters (including spaces) in a cell, then count all the characters excluding spaces, and subtract the latter from the former. Then, you'll have the number of spaces in a cell!

3. Removing the Spaces From the Cell

In order to count the characters in a cell and exclude the space character, you can simply remove the spaces from the cell and then count them. To do this, you can substitute the space character with a blank with the SUBSTITUTE function. This will basically remove the spaces from a cell.

4. Empty Cells

Lastly, to prevent the formula from counting the spaces in a cell full of spaces only, you can use an IF statement that returns a zero for cells that don't have anything but spaces in them. You can check for these cells by trimming the spaces in them, removing the extra spaces in them, and then counting the letters. If the count of letters is zero, then the cell doesn't have any words in it.

Putting the Formula to Use

An example of the formula counting the words in a range of cells in Excel.

Now that you understand what the formula does and how it works, it's time to see it in action.

  1. Enter a string of text in each of the cells A1 to A3.
  2. Select cell D2 and in the formula bar, enter the word count formula:
            =SUM(IF(LEN(TRIM(A1:A3))=0,0,LEN(TRIM(A1:A3))-LEN(SUBSTITUTE(A1:A3," ",""))+1))
        
    Notice that the range in the formula has been replaced with A1:A3, which is the actual range.
  3. Press Ctrl + Shift + Enter on your keyboard.
  4. Observe the number of words in cell D2.

Related: How to Create Professional Looking Excel Spreadsheets

Get a Count of Your Words

Though Excel has no function meant to count words, with a little bit of mixing and combining you can use basic Excel functions to get the word count in your Datasheet. Counting words could be your final goal or the result could be an input for another one of your Excel formulas.

Learning the basic functions in Excel gives you more to work with and allows you to build more sophisticated formulas. A cheat sheet to all essential Excel functions might be just what you need.