While there is no direct formula to get the word count in Google Sheets, you can easily get it by using a combination of a few functions.
Although using a formula to get the word count is not as convenient as the in-built feature in Google Docs, the formulas are pretty straight forward and even allow a lot of customization (examples covered later in this tutorial)
In this tutorial, I will show you how to count the total number of words in a cell using a formula, and some variations of getting the word in specific situations.
Get the Total Word Count in a Cell
Suppose you have a dataset as shown below and you want to get the word count in each.
The following formula will give you the word count:
=IF(A2="","",COUNTA(SPLIT(A2," ")))
The above formula uses the SPLIT function, which separates the text string based on the delimiter.
In this formula, my delimiter is a space character, so the SPLIT function would give me an array of text string delimited by the space character. The COUNTA function then simply counts the total number of these delimited values.
And the reason I have used the IF formula is to make sure I get a blank when there is no text in the cell. If you don’t use the IF function, the formula will return 1 even when the cell is empty.
This, in turn, gives us the word count.
Note that this formula works even if you have any leading, trailing, of double spaces in between the words.
Get Word Count of an Entire Column
You can also extend the above formula to give you the word count of all the text in an entire column.
The below formula would do this.
=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A4," "))))
It’s almost the same formula where I have added the SUM formula and changed the range is the SPLIT part to include a range instead of a single cell.
And since I am playing with a range of cells, you need to use ARRAYFORMULA to make sure it gives the right result.
Note that you don’t need to stick to just one column to use this formula. you can use a range of cells that can span rows and columns (but it needs to be contiguous).
Get the Word Count of With/Without Specific Words
Since the SPLIT function has made it so easy to quickly split a sentence into words, we can further enhance it to give us the count of specific words in a sentence.
For example, suppose you have the following data set and you want to find the occurrence of the word THE in each cell.
You can do that by using the below formula
=IF(A2="","",COUNTIF(SPLIT(A2," "),"the"))
The above formula uses a similar SPLIT function to get a list of each word (that is separated by a space character). And with this set of words, I have used the COUNTIF function to only count the words that match the criteria word – which is ‘the’
The same logic can apply when you want to find the word count while excluding a specific word or text string.
The below formula would give you the word count of the words in each cell while ignoring the word ‘THE’
=IF(A2="","",COUNTIF(SPLIT(A2," "),"<>the))"))
Get the Word Count When Words are of a Specific Length
In case you want to get the word count of only those words which are above a specific character length, you can do that as well with formulas in Google Sheets.
Suppose you have the below data set and you want to get the word count of words that are at least four characters long.
The below formula will do this:
=ArrayFormula(COUNTIF(LEN(SPLIT(A3," ")),">3"))
The above formula uses the SPLIT function to get all the words separated by a space character. The LEN function then finds out the length of each word.
This array of numbers (which is the length of each word) is used with the COUNTIF function, and it only counts those elements where the value is greater than three (as this is what we have specified as the criteria in COUNTIF).
Count the Number of Lines in a Text in a Cell
We can also extend the same logic and use the SPLIT function to even count the number of line breaks in a text string in Google Sheets.
Suppose you have a dataset as shown below and you want to count how many lines are there in each cell. Since these lines are separated by a line break, what we need is to count the number of line breaks.
The below formula will give us the line count in each cell:
=COUNTA(SPLIT(A2,CHAR(10)))
The above formula uses CHAR(10) as the criteria. CHAR(10) returns the line feed character which is inserted when you insert a line break in Google Sheets.
This formula can be useful when you get inconsistent data and you want to make sure everything is correct. For example, in this example, while all the address is in the same format, these have inconsistent line breaks, and you can use the above formula to make sure you spot the ones with more/fewer line breaks.
Online Tool to Get the Word Count
While you can use the above-covered formulas to get the word count, you may not want to insert a column and use the formula if you only have to do it once in a while.
In such cases, you can use many of the online word counter available – such as this one.
All you need to do is copy and paste the text in the box and it will instantly give you the number of words and the number of characters in the text.
Pro Tip: You can also use Google Docs to quickly get the word count. Just copy and paste the text in any blank Google Docs document and use the keyboard shortcut Control + Shift + C (press all together) to get the word and character count.
You may also like the following Google Sheets tutorial:
- Convert Formulas to Values in Google Sheets
- How to Delete Empty Rows in Google Sheets
- How to Change Text Case in Google Sheets (Upper, Lower, Proper, or Sentence case)
- How to Transpose Data in Google Sheets
- How to Count Cells with Specific Text in Google Sheets
- Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
