What is Word Count in Google Sheets?
Google Sheets not only contains numbers but may contain text and well. At times, we would require the task of counting the number of words in a cell or range for our calculations. In such cases, we do not have a direct function to do a word count in Google Sheets. Therefore, by applying a few formulas, we can easily do a word count.
In this article, we learn how to count words in Google Sheets. This scenario arises when there are multiple words in cells, and you may need to have their count instead of the string length in Google Sheets. In this simple example, we have counted the number of words in A1 by writing the formula below.
=LEN(A1)-LEN(SUBSTITUTE((A1), ” “, “”))+1
Here, we have used LEN to count the total characters. LEN(SUBSTITUTE((A1), ” “, “”)) counts all the characters minus the spaces. Subtracting both, we get the number of spaces. A sentence always has one word more than spaces; hence, we add 1 at the end.
Key Takeaways
- To perform a word count in Google Sheets, there is no function, but it can be easily done by combining a few functions. There are plenty of formulas that can count text.
- One of the most commonly used formulas to count the number of words in a cell is using the following formula:
- =LEN(cell address)-LEN(SUBSTITUTE((cell address), ” “, “”))+1. This formula counts the blank space between the words and adds one to find the total number of words.
- Other functions that can be used to count words or specific words in a range of cells include COUNTIF, TRIM, ARRAYFORMULA, SPLIT, etc.
- These functions can be used to customize word count formulas, such as excluding certain characters or words, counting repeated words, counting a particular word’s occurrences, etc.
How to Count Word in Google Sheets?
As seen above, there is no direct formula for word count in Google Sheets. However, a little manipulation of the existing functions enables us to count them. This article will examine a few different methods for counting words. First, let us look at how to enter the formula step-by-step manually.
Enter the text in a required cell.
Step 1: Click on the cell where the result must be displayed. Here, we choose B1. The formula we will choose to enter here is based on:
Type the following formula in cell B1. =LEN(A1)-LEN(SUBSTITUTE((A1), ” “, “”))+1
Step 2: Press Enter. You get the result. When you count the number of words in the sentence, you can check that the result is correct.
As explained in the image in Step 1,
- we have used LEN to count the total characters.
- LEN(SUBSTITUTE((A1), ” “, “”)) counts all the characters minus the spaces.
- Subtracting both, we get the total number of spaces.
- A sentence always has one word more than spaces; hence, we add 1 at the end.
Examples
We have shown you one method using the LEN and SUBSTITUTE functions. Let’s look at other methods for counting words in Google Sheets one by one.
Example #1 – Count the Total Number of Words in a Range
We have used one method for the word count of a specific cell. What is the method for counting all the words in a particular range? Applying the above formula by dragging across the range and doing a SUM of the total might seem cumbersome, especially when dealing with large spreadsheets. Here is a simpler method for counting the total number of words in a range.
Let us count the total words in a particular range without spaces.
Step 1: Enter the data in your spreadsheet.
Step 2: Now, let us slowly build the formula. It is for counting the words in this range.
- First, we split the text in each cell by spaces using the formula:
- SPLIT(A4:A8,” “).
- Even though we have used the range, unless we apply ARRAYFORMULA, we get the split only for the cell.
- Then, we use the following formula to count the number of entries created by the split which are not empty.
- (COUNTA(SPLIT(A4:A8,” “)).
- Now, we must apply it to the entire range. Use SUM to find the total words of all the cells in the range and ARRAYFORMULA to apply it to the entire range.
- =ARRAYFORMULA(SUM((COUNTA(SPLIT(A4:A8,” “)))).
- Apply this formula in cell B4.
Step 3: Now, press Enter. You get 41, which is a count of the total words in the range.
Step 4: However, some sentences may account for blank cells. So, we subtract a count of such blank cells, if any, to get the final word count.
=ARRAYFORMULA(SUM(COUNTA(SPLIT(A4:A8,” “)))-COUNTBLANK(A4:A8))
Step 5: Press Enter to get the total count of words in a range.
Example #2 – Count Specific Words in a Range
Now, let us study a Google Sheets formula that helps count specific words in a cell or range.
Now, let us look at how many times the word ‘panic’ appears in the range. To do this, we will use some of the same functions we used in the other examples, like LEN, SUBSTITUTE, and possibly LOWER.
Step 1: First, we use the function LOWER to convert all the text to lowercase, which counts regardless of case
(LOWER(A1:A4).
Now, we may use SUBSTITUTE to replace all the ‘panic’ with empty strings.
SUBSTITUTE(LOWER(A1:A4), “panic”, “”)))
Let us apply this and see what happens in cell B1.
You can see how the ‘panic’ has been replaced by empty spaces.
Step 2: Now comes the important step. Let us subtract the number of characters without the word ‘panic’ from the total string length, which includes the word ‘panic.’
When you drag this formula, you get a count of all occurrences of ‘panic’ in each row.
Since, it is a count of the number of characters till here, you can observe that for ‘panic,’ you get 10 if it occurs twice in a cell, 5, if it occurs once in a cell, etc.
Step 3: To count the occurrence of this word and not the number of characters, it’s a simple mathematical formula to divide the total by the string length of ‘panic.’
Replace the following formula in cell B1.
=(LEN($A$1:$A$4) – LEN(SUBSTITUTE(LOWER($A$1:$A$4), “panic”, “”)))/LEN(“panic”)
Step 4: Drag the formula across till A4.
Use SUMPRODUCT to find the total sum of the range.
=SUMPRODUCT(LEN($A$1:$A$4) – LEN(SUBSTITUTE(LOWER($A$1:$A$4), “panic”, “”)))/LEN(“panic”)
Example #3 – Count Repeated Words in a Range
Let’s examine the dataset below, which shows an example of counting the number of repeated words in a range.
Step 1: First, let us use the function UNIQUE to count the number of unique words here.
Step 2: Once you get that, you apply the function COUNTIF in Google Sheets. It is to find the number of each of these unique words count in Google Sheets in the range.
Step 3: Now, to count the repeated words, find the SUM using the SUMIF setting a condition for values > 1, as shown below.
Enter the following formula in C6.
=SUMIF(C1:C5,”>1″)
Thus, as seen, you have a count of the repeated words in the range.
Example #4 – Count the words in a Cell Based on a Criteria
Another interesting example is counting the words in a particular cell. Look at the cell below. Here, we will count the words in the cell without the occurrence of the word “it” as a criterion.
Step 1: First, apply the formula that we applied in Example 1, if you can recall.
SPLIT(A1,” “) – Splits the words into a column each.
Next, instead of COUNTA, which we applied in Example 1, we use COUNTIF.
It is to apply a specific condition.
(COUNTIF(SPLIT(A1,” “),”<>it”).
Here, we apply the function COUNTIF(range, criteria), where the criterion is to eliminate the word “it” from the count.
Step 2: Now, apply this function in cell B1.
Step 3: Press Enter. You get the result 12. You can see that for a word count of 16 in the cell, four has been subtracted, which is the total number of “it.”.
Thus, you can count the words of a cell with specific criteria as well.
Important Things to Note
- To perform word count in Google Sheets in multiple cells simultaneously, you can use ARRAYFORMULA. For example, like =(ARRAYFORMULA(SUM(COUNTA(SPLIT(A1:A5,” “)))
- We use the LEN function to count the number of characters, including spaces. To get the number of a particular word, divide this character count by the number of characters in the specified word.
Frequently Asked Questions (FAQs)
There are different ways to perform word count in Google Sheets. One requirement could be to count words in an entire range.
The following is the generic formula used for it.
=ARRAYFORMULA(SUM(COUNTA(SPLIT(range,” “)))
Here, SPLIT first splits the words in the cells in vertical columns.
Next, we count the number of words using COUNTA.
We use the ARRAYFORMULA and SUM to find the SUM of the words in all the cells within the range and apply the formula simultaneously to the range.
To count the number of words in a cell, you can use the LEN, TRIM, and SUBSTITUTE functions as a combination.
The following is a sample of the formula:
LEN(TRIM(cell address))-LEN(SUBSTITUTE(cell address,” “,””))+1
Here, cell address is the address of the cell where you want the word count.
As an example, to count words in cell A1, we use:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1.
When performing word count in Google Sheets in a cell/range, make a note of the following points.
1. Look out for empty cells: Whenever you write a formula, look out for empty cells and subtract them using functions like COUNTBLANK. If you attempt to count an empty cell, the formula might return an error or the wrong count.
2. Punctuations: If you are counting specific words, the presence of punctuation could lead to inaccurate results if it is not handled in your formula.
3. Special characters: The same goes for special characters that can interfere with counting.
4. Syntax errors: Watch out for syntax errors in your formulas as you will be using lengthy formulas. Ensure your formulas are correctly structured.
5. Array Formula: Be careful when using array formulas, as it may result in unexpected outputs.
Download Template
This article must help understand the ISODD in Google Sheets formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Word Count in Google Sheets. We learn how to do a Word Count in Google Sheets with detailed examples and working template. You can learn more from the following articles.
Leave a Reply