In this tutorial, we cover how to count words in Google Sheets. While spreadsheets are commonly known for storing numbers and calculations, it's not uncommon to store words and text. Counting the total number of words in google Docs is easy as it comes with a word count tool. However, with Google Sheets, you need to do a bit of extra work by using a formula.
Step 1: Click on an empty cell.
Step 2: Paste =COUNTA(SPLIT(cell number," ")) in the empty cell.
Replace cell number with the cell you want to count words in.
Step 3: Press Enter.
Here, we counted all of the words in cell D9
Let's break down the formula:
As we can see, cell D9 has 29 words. Breaking down the formula =COUNTA(SPLIT(D9," "))
The COUNTA function counts cells containing any information.
The SPLIT function in Google Sheets separates a text string or value as per a set delimiter (here, the delimiter is a space " ").
We need to use the SPLIT function to ensure we count all of the words. If we used COUNTA without the split function to separate words based on blank spaces then the count would be 1 instead of 29 in our example above.
To see word count of each cell while excluding empty cells follow these steps:
Step 1: Click on a blank cell where you want to display the word count.
Step 2: Copy or paste the formula =IF(cell no=""," ",COUNTA(SPLIT(cell no," "))).
Replace cell no in the formula above with the cell for which you want to get the word count.
Step 3: Press Enter.
As you have noticed, method 2 is an extended version of method 1, i.e =COUNTA(SPLIT(cell no,""))
Adding the IF command determines whether the cells are empty; if there is an empty cell, Google Sheets will not count the cells as a word. However, Method 1 will count empty cells as a word.
In the picture above, D13 is a blank cell, so the IF command leaves the cell blank as it does not count D13 as a word.
Count words in a sentence or paragraph by counting only the total spaces in the cell:
Step 1: Click on a cell where you want to display the result.
Step 2: Copy and paste the formula =LEN(cell no)-LEN(SUBSTITUTE((cell no), " ", ""))+1
Replace the instances of cell no in the formula above with the cell for which you want to get the word count.
Step 3: Hit Enter.
In the example below, D16 denotes the cell containing the words you intend to count.
Dissecting the formula
The LEN function in Google Sheets is used to count the length / number of characters in a string.
LEN(D16) counts the total number of characters in the cell
LEN(SUBSTITUTE((D16)," ","")) formula eliminates all spaces from the cells, thereby counting only the leftover characters
After subtracting the second formula from the first one, you will get the number of spaces in the particular cell.
Add +1 at the end because a space is only written after a word and the first word in the sentence will be ignored.
You can use Methods 1 and 2 to count each specific cell to get a total word count. However, this can be a long process, especially while dealing with more substantial spreadsheets in Google Sheets. This method is the fastest way to get the word count for a specific column.
This example Google Sheets formula allows you to calculate the total words for a column range without blank cells.
=ARRAYFORMULA(SUM(COUNTA(SPLIT(D30:D35," ")))-COUNTBLANK(D30:D35))
To calculate the total words in an entire column in Google Sheets, follow the steps below:
Step 1: Select an empty cell.
Step 2: copy the formula =ARRAYFORMULA(SUM(COUNTA(SPLIT(D30:D35," ")))-COUNTBLANK(D30:D35)) and paste it in the cell you you want the results to appear.
Replace the range D30:D35 with your range.
Step 3: Hit Enter
In Google Sheets, this formula ignores the blank cells without counting them as 1 and counts the total words in each column to count the words of each column cell.
*It’s important to ensure that you include a blank space between the “ “ in this portion of the formula so that you count each individual word. COUNTA(SPLIT(D30:D35," ")))
In this example, we will count how many times the word "and" occurs in a particular cell in Google Sheets. You can use this formula to count for other words too.
To find the number of times a particular word occurs in a cell, you need to follow the steps:
Step 1: You need to click an empty cell
Step 2: Type =(LEN(cell no)-LEN(SUBSTITUTE(LOWER(cell no), " and","")))/LEN("and")
Substitute cell no with the cell number you want to count the words in. Replace the instances of and with the word you want to count.
Step 3: Hit enter.
D20 is the cell number containing the words you intend to count.
Let's dissect the formula:
(LEN(D20) gets the total number of characters in the cell. In D20 the total characters is 324.
Since LEN is a case-sensitive function in Google Sheets, you need to ensure all text you intend to find is either upper case or lower case. If you ask the LEN function to search for "and," it won't recognize words written as "AND," "anD', "And" and so on. To ensure you find all the words containing "and," you need to convert all the words into either upper case or lower case.
This formula uses LOWER(D20) to convert everything in cell D20 to lowercase.
Then we use (SUBSTITUTE(LOWER(D20), " and", ""))) to eliminate all the occurrences of "and" and replace them with empty strings (""). We have added blank space before ”and” here to avoid counting instances of “and” in other words.
The instance of LEN before (SUBSTITUTE(LOWER(D20), " and", ""))) counts the number of characters in the cell excluding instances of “and”. In this case the total number of characters is 315.
Finally all of the characters not containing the word "and" are subtracted from the string length. So, in the end, the number we get includes all characters containing "and" in each row.
324 - 315 = 9
To get the word count for “and” we need to divide 9 (the total characters for instances of and in the cell) by the number of characters in the word “and” which is 3.
Instead of manually entering a numerical value we can simply write /LEN("and")
If you enjoyed this article, you might also like our article on how to count colored cells in Google Sheets or our article on how to count unique values.
If you want to learn how to send mass emails from Google Sheets, we also suggest checking out our detailed guide.
Use our recurring deadlines tracker to easily set up custom reminders from your spreadsheet in just a few clicks.
