Google Sheets doesn’t have a Word Count button like Google Docs, but a short formula does the job. You can count the words in a single cell, total the words across a whole column, or count how many times a specific word appears. This guide gives you copy-and-paste formulas for each case, explains how they work, and covers common problems like extra spaces and blank cells.
Quick Answer
To count the words in cell A2, enter this formula in another cell:
=IF(LEN(TRIM(A2))=0,0,COUNTA(SPLIT(TRIM(A2)," ")))Drag the fill handle down to count words for the rest of the column, and use =SUM(B2:B100) for a total.
Count Words in One Cell
- Click an empty cell next to your text, for example B2.
- Type =IF(LEN(TRIM(A2))=0,0,COUNTA(SPLIT(TRIM(A2),” “))) and press Enter.
How it works: TRIM removes extra spaces, SPLIT breaks the text into separate words at each space, and COUNTA counts the pieces. The IF part returns 0 for empty cells instead of 1.
Alternative Formula
=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)This counts the spaces between words and adds one. It gives the same result and also works in Excel.
Count Words in a Whole Column or Range
Use one formula for the total without a helper column:
=SUMPRODUCT(IF(LEN(TRIM(A2:A100))=0,0,LEN(TRIM(A2:A100))-LEN(SUBSTITUTE(TRIM(A2:A100)," ",""))+1))Change A2:A100 to your range. If the formula returns an error, wrap it in ARRAYFORMULA() or press Ctrl + Shift + Enter.
Count Words in Every Row Automatically
Put this in B2 to fill the whole column at once, including new rows:
=ARRAYFORMULA(IF(LEN(TRIM(A2:A))=0,"",LEN(TRIM(A2:A))-LEN(SUBSTITUTE(TRIM(A2:A)," ",""))+1))Count How Many Times a Specific Word Appears
- Cells that contain the word: =COUNTIF(A2:A100,”*apple*”).
- Cells that exactly match: =COUNTIF(A2:A100,”apple”).
- Every occurrence, including several in one cell: =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),”apple”,””)))/LEN(“apple”)).
The last formula also counts the word inside longer words (like “pineapple”). For more, see how to count how many times a word appears in Google Sheets.
Count Characters Instead
- Characters including spaces: =LEN(A2)
- Characters without spaces: =LEN(SUBSTITUTE(A2,” “,””))
Troubleshooting
- Count is too high: text may contain line breaks or double spaces. Use SPLIT(TRIM(A2),” “&CHAR(10)) to split on line breaks too, or clean the text with Data > Data cleanup > Trim whitespace.
- Empty cells show 1: make sure the IF check for empty cells is included.
- Numbers counted as words: formulas count anything separated by spaces, including numbers and symbols.
- Hyphenated words: “well-known” counts as one word; that matches Google Docs’ word count.
Frequently Asked Questions
Is there a built-in word count in Google Sheets?
No. Tools > Word count exists in Google Docs only. Copying text from Sheets into a Doc and using Ctrl + Shift + C also works for a quick check.
Do these formulas work in Excel?
The LEN/SUBSTITUTE formulas do. SPLIT is specific to Google Sheets; Excel 365 has TEXTSPLIT instead.
Can I count words in a selection quickly?
Not directly. The status bar shows Count and Sum for selections, but not words.
Summary
- Use =IF(LEN(TRIM(A2))=0,0,COUNTA(SPLIT(TRIM(A2),” “))) for one cell.
- Fill down and SUM for a total, or use SUMPRODUCT for a range.
- Use COUNTIF to count cells with a specific word.
- Use LEN for character counts.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.