Count Total Words in a Cell – Excel & Google Sheets

This tutorial will teach you how to count words in a cell in Excel and Google Sheets.

count words in cell Main Function

Counting Words

There is no built-in Excel function to count the number of words in a cell. However, we can count the number of spaces, which can tell us how many words are in a cell:

count words in cell EX 01

Let’s see how this formula works.

Remove All Spaces

First, we use SUBSTITUTE Function to remove all spaces from the text string.

count words in cell EX 02

Then we determine the length of this space-free string with the LEN Function.

count words in cell EX 03

We will compare the string length before and after removing the spaces, to count how many spaces the cell contains.

Remove Extra Spaces

Next, we want to express this string as we normally would (i.e. containing only single spaces between words). The TRIM function removes extra spaces from a text string, leaving a single spaces between each word, so all leading, trailing, and repeated spaces will be excluded.

count words in cell EX 04

Then we determine the length of this normally-arranged text with the LEN function.

count words in cell EX 05

Finding the different between the two lengths (with and without spaces) and adding one (since there is no space after the last word) gives us the word count of the cell:

count words in cell EX 06

Combining these steps gives us the formula:

count words in cell EX 01

 

Google Sheets –Count Total Words in a Cell

All of the above examples work exactly the same in Google Sheets as in Excel.

count-words-in-cell-Google-Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!