How to Count the Number of Words in Google Sheets

How to Count the Number of Words in Google Sheets

You can easily count the number of words in a text string in Google Sheets. All you need is some know-how of the Text Functions (LEN and SUBSTITUTE).

Count the Number of Words in Google Sheets

Suppose you have a few text strings as shown below:

Count the Number of Words in Google Sheets - Data

There are different text strings here of varying length.

Now to count the number of words, instead of counting the words, we can count the number of spaces in between the words.

For example, in ‘Hello World’, there is one space, so we would know that the number of words are two. Similarly, in ‘Have a Good Day’ has three spaces and four words.

Here is the formula that will give us the word count:

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

Count the Number of Words in Google Sheets - Formula

How this Works?

  • The LEN(A1) part of the function would give you the total number of characters in the text string (including the spaces).
  • The LEN(SUBSTITUTE(A1,” “,“”)) part first removes all the spaces by using the SUBSTITUTE function. It then counts the length of the characters in the text string.
  • The value 1 is added to the difference of these two formulas to get the total number of words.

But what if there are more than one space characters between words?

Fair question.

In that case, this formula would give you a wrong result.

Counting the Number of Words (with more than one space in between words)

Suppose you have a dataset as shown below:

Count the Number of Words in Google Sheets - more space

You can see that the text is the same, but since the number of spaces between the words vary, the word count given the formula used above gives a different result.

There is a simple fix to this problem – the TRIM function.

TRIM function removes all the extra leading, trailing, and double spaces between words. Only a single space character between words is allowed.

Here the formula that will give you the right result:

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

Count the Number of Words in Google Sheets - Using TRIM

All we have done is replaced the cell reference A1 with TRIM(A1). This makes sure that the text in the cell has been treated for leading, trailing, and double spaces between words.

You may also find the following tutorials useful: 

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X