It’d be nice if Google had a built-in word counter, but for now, you’ll have to do a little extra work to get a word count in Google Sheets. Whether you need to just count the number of words in a cell, a column, or even the entire sheet, it’s possible. You won’t find this option in the Google Sheets menu, but thanks to a some function magic, you can stop doing this task manually and let Google Sheets do a word count automatically.
Good to know: You can more easily go through information in your Google Sheets by changing the cell color.
Count Words in a Single Cell With SPLIT
The easiest way to get an automatic Google Sheets word count is to use the SPLIT function with the COUNTA function. The SPLIT function splits apart text based on a set delimiter. To count words, use a space, or ” “, as your delimiter. As a side note, SPLIT will also split apart each individual letter if you also want to get a character count versus a word count.
While SPLIT breaks a string of text into individual words, COUNTA counts each word. The combination gives you a word counter.
You’ll also combine these two in an IF statement. This allows empty cells to return an empty value versus providing a count of one.
To get started:
- Create a new column or designate a cell in your sheet for the word count formula.
- Copy the following formula into the cell, or first empty cell of your word count column. Replace “A2” with the cell you want to count.
=IF(A2=0," ", COUNTA(SPLIT(A2," ")))
- The word count appears in the cell. Simply copy this formula for each individual cell you want to count.
If you don’t have to worry about any empty cells, you can simplify the formula to just:
=COUNTA(SPLIT(A2," "))
Both formulas work even if you have double spaces between words or sentences.
Tip: Google Sheets can be a useful tool for going over your budget. Try one of these budgeting templates to get started.
Count Multiple Cells, Rows, and Columns With SPLIT
A single cell is nice, but if you need a Google Sheet word count for multiple cells, rows, and columns, you need to build on your formula to calculate a range versus one cell. In this case, you’ll use ARRAYFORMULA, which allows you to work with a range and get the results in a single cell. Dive deeper into ARRAYFORMULA and other advanced Google Sheets formulas with this list.
- Select the cell you want to place the final word count in.
- Copy the following formula into the cell:
=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A10," "))))
- Adjust the range to cover whatever you need. This includes a single column, a row, or even multiple rows and columns. When working with additional columns, simply add a second COUNTA section. For example, if I want to add all of Column A and Column B in a single cell:
=ARRAYFORMULA(SUM(COUNTA(SPLIT(A2:A10, " "))+(COUNTA(SPLIT(B2:B10, " ")))))
You can actually get the same result without the SUM:
=ARRAYFORMULA(COUNTA(SPLIT(A2:A10, " "))+(COUNTA(SPLIT(B2:B10, " "))))
- If you have blank cells, the above formulas will count each blank cell as 1. So, your total won’t be correct. If you want to avoid this, use this formula instead:
=COUNTA(SPLIT(TEXTJOIN(" ", true, A2:B11), " "))
Count Words in Google Sheets With LEN
While SPLIT is the easier method, you can also get a Google Sheets word count using the LEN function mixed with SUBSTITUTE. Usually, LEN would be used to count the number of characters in a cell. But by using SUBSTITUTE, you can count just the spaces. This means for each space, there’s a word. Since you won’t have a space after your last word, you’ll need to add 1 to the formula to account for the last word in the cell.
- Choose the cell you want to place your total in.
- Copy the following formula using your desired cell:
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1
The main problem with this formula is with possible double spaces between words. The extra space counts as another word. If you have double spaces or are prone to accidentally typing double spaces, don’t use this one.
Good to know: yes, it’s possible to add a drop-down list in Google Sheets. This guide shows you how.
Count or Ignore Specific Words
Want to fine tune your word count? You can use the SPLIT function to only count a specific word or completely ignore that word in your count. For instance, if you don’t want to repeat yourself too much in your sheet, you could choose to just count the word you fear you are going to repeat. Or perhaps you’d prefer to see what the word count would be if you removed a specific word from a cell.
- Choose the cell you want to place your formula in.
- Copy the following formula, replacing the word “you” with your desired word:
=IF(A2=" "," ",COUNTIF(SPLIT(A2," "),"you"))
- Drag the formula down from the first cell to get a count for each individual cell. In my formula, I counted each instance of the word “you.”
- To ignore the word “you” or any other word of your choice, change the formula to:
=IF(A2=" "," ",COUNTIF(SPLIT(A2," "), "<>you"))
The downside is when you have an empty cell, as it will count as 1. It’s best to ignore results in empty cells.
Use Google Docs
For smaller datasets, you may find it easier to simply copy and paste your data into Google Docs and use the built-in word counter tool. This is ideal if you rarely need to count words in your spreadsheet. You can copy a single cell, column, row, a mixture, or the entire dataset to be counted by following the steps below.
- Open a blank Google Docs document.
- Open your sheet and highlight the cell(s) that include the words you would like counted. Press Ctrl + C to copy the text. If you want everything, press Ctrl + A followed by Ctrl + C.
- Open the blank Google Docs document and press Ctrl + V to paste the text. It’s your choice whether you would like to keep it as a table.
- Go to “Tools -> Word count” or press Ctrl + Shift + C to get the total word count on the page. If you like that keyboard shortcut, try these shortcuts too.
- You’ll see a box appear with the total word and character count.
Tip: Google Docs can also provide an easy way to create invoices. Start out with one of these invoice templates.
Frequently Asked Questions
Are there any third-party tools or extensions to get a word count in Google Sheets?
Outside of copying and pasting your text into online word counters, formulas and pasting into Google Docs are the most reliable methods. This is especially true if your sheet has any sensitive data on it that you don’t want third-party tools to have access to.
Can I count individual characters?
If you need to count characters versus words, you just need to use the LEN function. This is helpful if you’re using Google Sheets for planning social media posts or ads and want to ensure you’re under the required character counts. Place =LEN(cellreference) to count the characters.
For example, to count characters in cell A2, use =LEN(A2).
How are numbers handled?
If you have text and numbers in the same cell, numbers are counted as words. For instance, if you have the following address: 888 Some Road, the formulas above would count that as three words. Each group of numbers is a separate word. If you had a zip code in the address, that would be another word. If you’re working with money in your Google Sheets, learn how to automatically convert currency.
Image credit: Unsplash. All screenshots by Crystal Crowder.
Our latest tutorials delivered straight to your inbox
