4

How To Get Word Count in Google Sheets

Posted by Jamie on March 24, 2019

Whether you’re looking to throw together a quick financial spreadsheet, or you want to work together with a co-worker on an Excel-like document, Google Sheets is a great, web-based, and free alternative to Excel, an app used by millions around the country to load data into a document, view and share it, and use math to track specific content with a spreadsheet. While Google Sheets is primarily made to be used with numbers, in order to keep track of specific, data-based information, words are an important part of any spreadsheet. You need nouns in order to calculate, verify, and keep track of what each data point represents. Labels, months, days, products—they all need specific nouns in order to keep track of the content on the list.

Google Docs, like most word processors, allows you to easily check the list of words in any given document, giving you a count for both the entire document and the content you’ve highlighted using your mouse. It’s really easy to figure out how long a Google Doc document is in terms of word length, but unfortunately, at first glance, that tradition word count too isn’t present in the app. If you’re looking for a way to figure out how many words are in your Google Sheets spreadsheet, we’ve got an easy workaround that anyone can figure out and use. Let’s take a look.

Counting Text per Cell in Sheets

Sheets isn’t completely without the ability to count words within the document. While there’s no official word count tool, like you may find within Google Docs, Sheets does display a text count within specific columns, rows, and cells. To preview you this, click on a cell, whether it has a formula or just a label in it. You’ll see in the preview box at the top of the page what each cell has written, which makes it easy to modify the content on the page. In our preview photo below, we have a spreadsheet with a list of printers. This document has 78 lines containing a mixture of words and model numbers. With each model number, Google Sheets will not count these as words, only paying attention to “Xerox,” “Copystar,” etc.

performing-word-count-in-google-sheets-2

To count each word in a cell, we used a specific Google Sheets formula: ‘=COUNTA(SPLIT(A2, ” “))’. As seen in the screenshot above, using this formula automatically provides a count of the words in a specific cell. COUNT auto-counts the words in the cell, SPLIT counts everything separated by a space as an individual point of data (so that, for example, your content can be counted even if it’s just a number as a word) and the final section (seen here as A2) counts the cell you want to look at. As usual in Google Sheets, each specific cell allows you to easily track your data by using a grid-based location format (ie, A2 finds the second cell in the A column), so make sure you know which cell you’re looking at. Here’s how to use this formula:

  1. Highlight the  blank cell where you want to display your results.
  2. Paste the above formula into the formula bar above the column header.
  3. Modify the column letter and beginning and end cell numbers.
  4. Click in your display cell or hit Enter to apply the formula.

After following these instructions, Sheets will automatically give you the result for your word count in a specific cell. You can also use the ArrayFormula tool in Sheets in conjunction with ‘=ARRAYFORMULA(sum(counta(split(range,” “))))‘ in order to give you a wider sum of your content, without having to drag the formula from cell to cell, with an example of that finished formula being ‘=ARRAYFORMULA(sum(counta(split(A16:A19,” “))))

Counting Text per Column in Sheets

You could use the cell method to count each specific cell to receive a total word count, but as you can imagine for larger documents, this will take longer than you might want. In order to properly provide the word count for your document using a much faster method, you can count your text in Sheet by using each column, instead of each cell. Since text is often split into columns, with rows often using numbers as data signifiers, it’s actually pretty easy to come to a total word count quickly when using the column formula, listed here:

=ArrayFormula(LEN(CONCATENATE(A2:A78&CHAR(47)))-LEN(SUBSTITUTE(CONCATENATE(A2:A78&CHAR(47)),”/”,””)))

Yes, it’s a much longer formula, but the method of using it is just as simple as you might expect from Google Sheets, just as we posted above. In our example photo below, the text begins in box A2, but we’ll be using B2 to paste our formula and track our info. Here’s how it works:

performing-word-count-in-google-sheets-3

  1. Highlight cell B2 in your Sheet.
  2. Paste the above formula into the formula bar above the column header.
  3. Modify the column letter and beginning and end cell numbers.
  4. Click in B2 to apply the formula.

You’ll need to remember to modify the formula above to make sure the numbers apply to the cells you’re looking to count. In our example formula, the column runs from A2 to A78; you’ll need to replace those number in order to properly count the content of your column. Also note that your column will be outlined in red, in order to show where your formula applies. The end result of your hard work will be show in cell B2, easily displayed and outlined for you to take advantage of in order to gain an idea of your word count. With each column counted, you can use these numbers (as seen below) to add together and gain an idea of your word count, quickly and easily.performing-word-count-in-google-sheets-4

***

Whether it’s to give the tables some context, to describe what you’re seeing, column headings, or simply to give an idea of what your data represents, you’ll always have some amount of words sitting in your Google Sheets document. While it’s unfortunate that your content can’t be automatically counted, as it can be within Google Docs, it’s not too hard to use the formula tool within Google Sheets to quickly and easily add content to your document and add up a certain word count. With just the quick application of a formula, you can access the data you want, whenever you want it.

Got any more ways to perform a word count in Google Sheets? Tell us below if you do—and don’t be afraid to keep requesting Google add a proper word count tool in Google Sheets!

4 thoughts on “How To Get Word Count in Google Sheets”

Rakesh says:
when we post this formula and hit enter button it shows error
=COUNTA(SPLIT(A2, ” “))
Reply
Mike says:
In fact counta won’t return the count of the array of split. Bit suprised to see this. Users will just get a fomula error
Reply
Stefano says:
Try also this
=COUNTA(SPLIT(CONCATENATE(A2:A);” “))+COUNTA(A2:A)-1
Reply
Firdaush says:
Hi,
I was wondering if you knew of a way to use Arrayformula with CountA
=COUNTA(SPLIT(A2, ” “))

That way I don’t have to drag down this formula. Do you know of a way to do it?

Reply
John says:
Hi There, I just tried, and you can use
=ARRAYFORMULA(sum(counta(split(A16:A19,” “))))
Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.