This post is going to show you all the ways you can count how many words are in your text data in Google Sheets.

Google Sheets is mainly a data analysis tool but occasionally, you might need to perform some word processor operations such as counting the number of words in a cell or column.

While there is no straightforward way or a specific feature in Google Sheets that allows you to find the word count of cells, you can take advantage of the powerful text functions to achieve this.

In this post, you will learn how you can use a combination of functions to count the number of words in Google Sheets.

Count the Number of Words in a Cell with the LEN and SUBSTITUTE Functions

= LEN ( text )

The LEN function typically returns the number of individual characters in a cell, including spaces.

On its own, LEN wouldn’t be able to count the number of words in a cell. We will have to introduce another function, the SUBSTITUTE function.

= SUBSTITUTE ( text_to_search, search_for, replace_with, [occurrence_number] )

The SUBSTITUTE function does the job of finding text and replacing it with another bit of text.

You can use the SUBSTITUTE function to create a version of your text without any space characters. You can then count the number of characters in your text with spaces and without spaces using the LEN function.

Since the LEN function returns the number of characters in a cell including the spaces, the SUBSTITUTE function will allow you to get the number of characters in a cell without the spaces.

Subtracting these two results and adding 1 to their output will give you the number of words inside the cell.

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

In the above formula syntax, LEN(A1) will return the number of characters in cell A1 which is 6.

LEN(SUBSTITUTE(A1," ","") will return the number of characters in cell A1 minus the spaces. That is because the SUBSTITUTE function has been used to remove the space within the text. Therefore, the output will be 5.

The number 1 is added to the syntax because the number of spaces in a sentence is one less than the total number of words.

The syntax returns the number of spaces in the cell and adding 1 to it will give you the number of words inside the cell.

You can copy and paste down the formula and get the word count of the other cells.

The drawback to the method is that the syntax is susceptible to leading and trailing spaces.

In the above example, it’s clear the number of words in each cell is two. But the output is showing different results.

That is a result of the trailing and leading spaces in each cell.

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

While you might be able to see the leading spaces, the trailing spaces are invisible. You can use the TRIM function, to clean up all leading and trailing spaces.

Count the Number of Words in a Cell with the COUNTA and SPLIT Functions

This is another method that you can use to achieve the same result.

= COUNTA ( value1, [value2, …] )

COUNTA returns the count of the numeric and string values in a range.

= SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

The SPLIT function separates a specified text string around a given delimiter and places each segment of the divided text string into separate cells.

It uses four arguments to carry out this operation, although only two of those are compulsory.

• text is the text you want to split.
• delimiter is the characters around which you want the text to split. The function will divide the text at the point where it finds this character.
• split_by_each can be used if you have more than one character in the delimiter argument, you can use it to instruct the function to either take the characters in the delimiter argument as one text string or as individual characters.

For instance, if you have go as the delimiter, the function will divide the text around g, then o because the split_by_each argument is set to TRUE by default.

But if you want to divide the text around go as one piece of text, you set the split_by_each argument to FALSE.

• remove_empty_text determines whether to output empty texts in the result. Like the split_by_each argument, it’s also optional and set to TRUE by default. When set to FALSE, empty cells are included in the result.

= COUNTA ( SPLIT ( A1, " " ) )

Compared to the LEN syntax, this syntax is much more simple to use.

The SPLIT function returns the two words in cell A1 since they are delimited by a space. The COUNTA function counts them and returns 2.

When you copy the formula down a range, any empty cell within the selected range will return 1.

There’s an easy fix to this though. Wrap the syntax around an IF function.

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

The ISBLANK function checks if the cell is empty. Then the IF function returns nothing if there is a blank or the regular word count otherwise.

Count the Number of Words in a Column with the ARRAYFORMULA Function

Once you have the word count of each cell, finding the word count in a column becomes easy. You can easily use the SUM function to aggregate the total word count.

= SUM ( B1:B4 )

If you don’t want to get the word count in each cell and only want to know the number of words in a column, you can use this syntax instead.

= ARRAFORMULA ( COUNTA ( SPLIT ( A1:A4, " " ) ) )

Using the syntax in an ARRAYFORMULA will return a total count for the entire range rather than a count for each row.

Remember that the SPLIT function returns each segment of the divided text into separate cells in a row.

When you use the syntax in the ARRAYFORMULA function, COUNTA will count the number of cells returned by all the rows.

With this method, you don’t have to find the word count of each cell before you can know the number of words in a column.

Count the Number of Words in a Cell Based on a Criteria

Suppose you don’t just want to count the number of words in a cell, but the number of times a certain word occurs in a cell.

This is how you do it using the COUNTIF functions.

= COUNTIF ( SPLIT ( A1, " " ), "to" )

This syntax counts the number of times the word to appears in a cell.

= COUNTIF ( SPLIT ( A1 , " " ), "<>to" )

This syntax counts the number of words that are not to in the cells.

Note: These counts are not case-sensitive.

Count the Number of Words in a Cell with a Custom Apps Script Function

The syntaxes described so far work great for counting words. But they’re long and complicated, and you can easily get an error or a wrong calculation.

Creating a custom function that will count the words in a cell will remove all these complications.

function WORDCOUNT(textInput) {
  var wordCount = textInput.split(" ").length;
  return wordCount;
}

Go to the Extensions menu and click on the Apps Script option to open your apps script code editor and paste the above syntax.

After you paste the script, click on Save. Then go back to your spreadsheet and refresh the browser.

= WORDCOUNT ( A1 )

The script will create a custom function called WORDCOUNT. The function will count the number of words in a cell without any long or complicated syntax.

Although the function is also susceptible to leading and trailing spaces, you can use the TRIM function to avert this.

= WORDCOUNT ( TRIM ( A1 ) )

The only other issue with the script is that it won’t work in an ARRAYFORMULA.

You will have to find the word count by cell and then add up the total to get the number of words in a column.

Count the Number of Words in Google Docs

This method requires no formula. You just copy the contents from your spreadsheet and paste them into Docs, and its word count feature will give you an instant count of the number of words.

Select the cells with the words you want to count, then press Ctrl + C or right-click and select Copy to copy the cells.

Go to Google Docs and open a new file.

In the new blank document, press Ctrl + V or right-click and select Paste.

If you don’t see the word count as you type, go to the Tools menu, and select Word count.

You can also use the shortcut Ctrl + Shift + C.

In the next window, you will see the number of words in the document.

You can also add a checkmark to the Display word count while typing option, so you don’t have to go through this route next time.

Conclusions

Finding the word count in a cell is a task you will carry out on occasion. Whenever the need arises, you can use any of these methods to complete your objective.

There are several formula-based solutions available that will give you a word count from a cell. You’ll even be able to count specific words in a cell.

You can also create your own custom word count function using Apps Scripts. This can greatly simplify the formula inputs.

If you want a one-off word count without the trouble of using formulas, you can even use the Google Docs word count feature.

What other method for getting the word count of a cell do you know? Let me know in the comments section!