How to Get the Word Count in Google Sheets

Sumit

Last updated April 13, 2023

You can easily get a Word Count in Google Sheets. All you need is some know-how of the Text Functions (LEN and SUBSTITUTE).

This Article Covers:

Word Count In Google Sheets – Method 1

Suppose you have a few text strings as shown below:

There are different text strings here of varying lengths.

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 is two. Similarly, ‘Have a Good Day’ has three spaces and four words.

Here is the formula that will give us the word count in Google Sheets:

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

How does this work?

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 is more than one space character between words?

Fair question.

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

How to Count Words in Google Sheets, (With More Than One Space in Between Words)

Suppose you have a dataset as shown below:

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. So, how do we get the Google Sheets word count?

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

The 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

All we have done is replace 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 before Google Sheets counts the word – or spaces in this case.

Use a Word Counter and Google Sheets

The simplest method to word count spreadsheets is to simply copy and paste the data into an online tool such as wordcounter.net. You could also just copy and paste the text into Google Docs and use the built-in word counter in that program.

Use the IF, COUNTA, and SPLIT Function in Google Sheets to Count Words

This is probably the most complex way to count cells other than sitting there and counting yourself. But, it is still handy if you need to know the word count of several cells in a row.

let’s take the following as an example:
As you can see we used this formula to count the words:

=IF(A2="","",COUNTA(SPLIT(A2," ")))

The parts of this formula work as follows:

The SPLIT function separates a text by a delimiter which in our example we are using spaces
The COUNTA function then counts the total number of “delimited values” – so the words between each space, as defined by the SPLIT function
The IF function is to prevent empty cells from returning incorrect values. If you omit the IF function you will instead get a return of 1 for each empty cell

To make this formula work for you, you just have to replace A2 with whichever cell has the words you wish to count.

Get the Google Sheets Word Count of an Entire Column

Similar to the above formula, you can use this one to count an entire column:

=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A4," "))))

You’ll notice that the split part now includes the range of A2:A4 instead of a cell value. This means it will delimit by spaces for the entire range.

The SUM function will add the values from each cell together.

We also use the ARRAYFORMULA function since we are working across multiple cells instead of the IF function.