Word Count in Google Sheets: A Formula That Handles Blanks
Blog / SPREADSHEETS
Google Sheets does not have the Tools → Word count command available in Google Docs. To count words in Sheets, split a cell's text at whitespace and count the pieces. A blank-safe formula prevents empty cells from adding a word to your total.
Paste this formula next to your text
If your text is in A2, put the formula below in B2. REGEXREPLACE normalizes ordinary spaces, tabs and line breaks into single spaces, TRIM removes outer spaces, and SPLIT separates the remaining text. IF returns zero if the cleaned result is empty.
This is a space-separated token count. It is suitable for a draft caption or a paragraph in a cell, but it is not a universal language-aware word count.
=IF(LEN(TRIM(REGEXREPLACE(A2,"\s+"," ")))=0,0,COUNTA(SPLIT(TRIM(REGEXREPLACE(A2,"\s+"," "))," ")))Try three examples before filling the column
'Plan a better article' returns 4. The same phrase with several spaces between its words also returns 4. A genuinely empty cell returns 0. A line break between 'better' and 'article' is treated as a separator rather than merging the words.
If your spreadsheet locale uses semicolons to separate formula arguments, replace the commas between arguments with semicolons. Do not replace the space inside quotation marks: that is the delimiter.
Total the words in several rows
Fill the B2 formula down beside the text rows, then use =SUM(B2:B100) for rows 2 through 100. Adjust the last row to match your sheet. Exclude a heading row unless you intentionally want its words included.
A helper column lets you compare each row's count with the text. If a row suddenly has an unexpected count, inspect that cell before trusting the grand total. Formula errors are not zero words: fix the source problem rather than hiding errors in the total.
Watch for nonbreaking spaces and punctuation
Web text may include a nonbreaking space that the whitespace expression does not normalize as expected. If a copied sentence is counted as one piece, replace CHAR(160) with an ordinary space using SUBSTITUTE before the main formula.
Hyphens do not split a token in this formula. A punctuation mark separated by spaces counts as its own piece. For example, 'ideas — examples' has three pieces. Use the destination's counting rules for applications, essays or publication submissions.
Word frequency is a different task
The number of words in a cell is not the number of times a particular word appears. COUNTA counts cells or split pieces; COUNTIF normally counts matching cells. Neither automatically counts every occurrence of a word inside a long paragraph.
For a quick prose check, paste the relevant text into the online word counter and review its keyword-density list for repeated words. For spreadsheet totals that must keep updating, retain the formula and record its counting convention alongside your data.
Frequently asked questions
Where is Word Count in Google Sheets?
There is no dedicated Word Count menu command in Sheets. Google documents that command for Google Docs; use a text formula in Sheets.
Does this formula count characters too?
No. Use LEN(A2) for the cell's character length; the word-count formula counts pieces separated by whitespace.