Count Words in a Cell or Range of Cells

Unlike the Word Count feature in Microsoft Word, Excel does not give us a readymade way to find out the number of words in a cell or a range of cells containing text. However, with the help of the SUBSTITUTE function/formula and the LEN function/formula we can easily work this out.

To get a word count of any cell or range of cells, we'll use a combination of SUBSTITUTE and LEN function/formulas.

SUBSTITUTE

The SUBSTITUTE function is used to replace specific text in a text string with a different string of text. The syntax for the SUBSTITUTE function is as follows:

=SUBSTITUTE(text,old_text,new_text,instance_num)

where new_text replaces old_text in a text string.

To see it in action, type the text Sales Data in cell A1. In cell B1, type the following function:

=SUBSTITUTE(A1, "Sales", "Cost")

Cell B1 will display the result Cost Data, replacing the old text, Sales, with the new text, Cost.

LEN

The LEN function is used to return the number of characters in a text string. The syntax for LEN is:

=LEN(text)

To see how this works, we will continue with the previous example, with the words Sales Data in cell A1. In cell B2, enter the following function:

=LEN(A1)

You will get the result of 10, because the text entry Sales Data in cell A1 has nine text characters and one space contained in it.

Putting It Together

Now, to find out the number of words in the cell, we can use a combination of the LEN and SUBSTITUTE functions.

Again, using the previous example, with the text Sales Data in cell A1, click in cell B3 and enter the following function:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

The LEN function will return the number of characters within the text Sales Data (10) and the SUBSTITUTE function substitutes the spaces between words with nonspaces, and so would return 9. The number of spaces (0) will always be 1 less than the number of words. The use of +1 takes care of this. Using this function will display the result of 2, because there are two words in cell A1.