Count the Words in a Cell

Excel & Google Sheets

How it works

A cell with N words contains N−1 spaces, so counting spaces and adding 1 gives the word count. The formula measures the length of the text, then measures it again with every space removed by SUBSTITUTE — the difference is the number of spaces. TRIM is what makes it reliable on real data: it removes leading and trailing spaces and collapses runs of spaces into single ones, so "quarterly budget review" still counts as 3, not 5. Without TRIM, every stray double space inflates the count.

TRIM(A2)

Cleans the text first — no leading/trailing spaces, no doubled spaces between words.

LEN(TRIM(A2))

Length of the cleaned text, spaces included.

LEN(SUBSTITUTE(TRIM(A2)," ",""))

Length with every space deleted. The difference between the two = number of spaces.

+1

N spaces separate N+1 words.

When to use it

Use it to enforce length rules on descriptions, spot one-word survey answers worth ignoring, or QA product titles that must stay under a word limit.

Common mistakes

• ✕An empty cell counts as 1 word.

  ✓The +1 fires even on nothing. Guard it: =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1).

• ✕Skipping TRIM.

  ✓=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1 counts every extra space as a word — " Ana Torres " comes back as 4 or 5 instead of 2. Always TRIM both LEN calls.

• ✕Text separated by line breaks instead of spaces.

  ✓Words split by Alt+Enter have no spaces between them. Convert breaks first: SUBSTITUTE(A2,CHAR(10)," ") inside the TRIM.