How To Get the Word Count in Google Sheets (3 Simple Formulas)

Counting words in Google Sheets is a valuable skill that will allow you to determine the number of words in a cell or range of cells, and this can be very helpful, especially when dealing with large amounts of text information.

Unlike in Google Docs, where there is a word count tool to assist in getting the exact number of words automatically, you will have to do a bit extra work in Google Sheets by using Simple formulas of a combination of functions such as LEN(), SUBSTITUTE(), COUNTA(), SPLIT(), TRIM() among others.

One can also use a Custom Apps Script Function, and even with access, you can use Google Docs to count words by copying and pasting the spreadsheet text into Google Docs.

How To Get the Word Count Per Cell in Google Sheets

The below methods will guide you through the steps to get the word count of cells in Google Sheets.

Method 1: Using the LEN and SUBSTITUTE Functions to Count Words in Google Sheets

The LEN() function in Google Sheets counts all the letters and spaces in a cell to tell you how many characters are there. But it can’t figure out the right number of words by itself. To fix this, you’ll need to use another function called SUBSTITUTE() that helps you handle the spaces.

The formula formed with these two functions will help you find the total number of characters in the text and then compare it to the number of characters when spaces are removed. The difference between these two values represents the number of spaces, which is then adjusted by adding 1 to give an approximation of the number of words in the text. Keep in mind that this formula might not be perfectly accurate for all types of text, especially if there are unusual spacing patterns or special characters.

Using the example below, let’s count the words in each cell using the LEN() and SUBSTITUTE() functions.

Step 1: Select the cell to display your result

Step 2: Insert the formula =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Step 3: Hit Enter and drag down the Fill Handle to copy the formula to other cells

Now Let’s break down this formula step by step using the text in cell A1: “Today is a beautiful day to go outside“

1. LEN(A1) – This part of the formula calculates the total number of characters (letters, numbers, symbols, and spaces) in the content of cell A1. And the total length of the text in cell A1 “Today is a beautiful day to go outside” is 39 characters.
2. SUBSTITUTE(A1," ","") – The SUBSTITUTE function is used to replace spaces (” “) in the content of cell A1 with nothing (“”). So, this part of the formula removes all the spaces from the text in cell A1.
3. LEN(SUBSTITUTE(A1," ","")) – Now, after removing the spaces, this part calculates the length (number of characters) of the text without spaces. When spaces are removed, the text becomes “Todayisabeautifuldaytogooutside” with a length of 32 characters.
4. LEN(A1)-LEN(SUBSTITUTE(A1," ","")) – This subtraction calculates the difference between the total length of the text in cell A1 and the length of the text without spaces. The difference between the total length and the length without spaces amounts to 7, which represents the number of spaces between words.
5. LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 – Finally, this part adds 1 to the count of spaces. The reasoning here is that the number of spaces between words is one less than the number of words. So, by adding 1 to the count of spaces, you get an estimate of the number of words in the text. Therefore, adding 1 to the count of spaces gives an approximation of the number of words. In this case, there are 8 words.

Thus, according to the formula, the text “Today is a beautiful day to go outside” is estimated to have 8 words. This approximation is based on the number of spaces between words and might not be perfectly accurate in all cases, especially with different spacing patterns or special characters.

NOTE: This method has one drawback in that, for situations where there are spaces before and even after the text in a cell, they will be counted as characters, and your result will not be the correct number of words in that cell, as illustrated below.

However, you can use the TRIM function to help you clear up the leading and most notorious tailing spaces because they are invisible. This is possible by using the following function inside the cell you want your result to be displayed,

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

As illustrated below.

Method 2: Using the COUNTA() and SPLIT() Functions to get Word Count in Google Sheets

This is an alternative method to achieving the same result above. Both the SPLIT and COUNTA functions in Google Sheets work together to efficiently determine the number of words in a cell’s text.

The SPLIT function breaks down the text in the cell into separate pieces, or words, using a designated separator, which is typically a space in this case. This transformation results in an array of words. Then, the COUNTA function steps in to count the non-empty elements in this array, which correspond to the words in the original text.

By using these functions in combination, you can quickly and accurately ascertain the number of words in a cell’s content without needing to resort to more complex calculations or character manipulation. This approach is particularly useful for simple and straightforward word count tasks.

Below is are the steps to count words in Google Sheets using the COUNTA() and SPLIT() functions.

Step 1: Select the cell to display your result.

Step 2: Insert the formula =COUNTA(SPLIT(A1, " "))

Step 3: Hit Enter 

In cell B1, the exact number of words in cell A1 will be displayed as your result, just as illustrated below.

Now let’s break down this formula =COUNTA(SPLIT(A1, " "))

Text in cell A1: “For context Google Sheets treats information in a single cell as a continuous string”

• SPLIT(A1, " "): This part of the formula uses the SPLIT function to divide the text in cell A1 into separate pieces, or “splits,” wherever there is a space (” “) in the text. This effectively separates the text into individual words as shown below:

1. “For”
2. “context”
3. “Google”
4. “Sheets”
5. “treats”
6. “information”
7. “in”
8. “a”
9. “single”
10. “cell”
11. “as”
12. “a”
13. “continuous”
14. “string.”

• COUNTA(...): This part of the formula uses the COUNTA function to count the number of non-empty items in a given range. In this case, it counts the number of separate words that resulted from the SPLIT function.
  • Count of non-empty items from the split text: 14

Therefore, when you put it all together, the formula =COUNTA(SPLIT(A1, " ")) counts the number of words in the text string in cell A1.

How to Get the Word Count in Multiple or Range of Cells in Google Sheets

Method 2 & 1 only address counting words within a single cell using the dynamic SPLIT() and COUNTA() functions and LEN() and SUBSTITUTE() functions respectively.

What if you want to get word count across numerous cells or entire ranges?

This can be done using ARRAY FORMULA.

This formula allows users to perform tasks across multiple cells or ranges with ease.

Thus, to find the word count of a range, entire columns, or entire rows, you’ll need the ARRAY FORMULA and other functions like SUM, COUNTA, and the SPLIT function.

Let’s say you want to count the number of words in a range of cells from A1 to A3. Below is the formula for that:

=ARRAYFORMULA(SUM(COUNTA(SPLIT(A1:A10, " "))))

Here’s how this formula works:

1. SPLIT(A1:A10, " "): This part of the formula splits the text in each cell of the range A1:A3 into words, just like we did before.
2. COUNTA(...): This part counts the number of non-empty items in each split text, giving the count of words for each cell.
3. SUM(...): Finally, the SUM function adds up the word counts from each cell in the range, giving you the total word count for all cells in the range.

Using this formula, you’ll get the total word count across all the cells in the range A1:A3.

Remember to adjust the range (A1:A3) to match the specific range of cells you want to count words in. Also, note that array formulas can sometimes slow down the performance of your spreadsheet, especially if you’re dealing with a large range of cells.

Conclusion

In conclusion, you can get the word count in Google Sheets easily using simple formulas, and by utilizing the LEN function to count the characters in a cell and subtracting the number of spaces, we can accurately determine the word count. 

Additionally, by combining this formula with other functions like SUBSTITUTE and TRIM, one can handle cases with multiple or leading/trailing spaces. 

Also, through the ARRAY FORMULA, you can easily count words in a column, and this method provides a quick and systematic way to obtain word counts in Google Sheets, which can be helpful for various purposes such as analyzing text data or tracking document lengths, among others.

FAQs

Can You Exclude Certain Words From the Word Count in Google Sheets?

Yes, one can exclude specific words from the word count in Google Sheets by modifying the formula slightly. Assuming the text in A1 is: “I ate an apple and an orange.” Here’s a formula to exclude the word “apple” from the word count: =COUNTA(SPLIT(REGEXREPLACE(A1, "apple", ""), " ")).

Can We Get a Live Word Count in Google Sheets?

Google Sheets has no built-in feature for live word counts like other word processing software. However, you can use a script to achieve this functionality.

Can I Get the Word Count for Multiple Sheets Within a Google Sheets Document?

One can get the word count for multiple sheets within a Google Sheets document by referencing each sheet name in separate formulas. You can duplicate and modify the word count formula to include the desired sheet name.