Text

Excel formula to count words in a cell

Count the spaces between words and add one. TRIM first so double spaces don't inflate the count.

All Excel versions and Google Sheets

The formula

Excel & Google SheetsExcelGoogle Sheets
=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)
In C2 → 5
Empty cell returns 0ExcelGoogle Sheets
=IF(LEN(TRIM(A3))=0,0,LEN(TRIM(A3))-LEN(SUBSTITUTE(A3," ",""))+1)
In C3 → 0
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.

Worked example

Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.

ABC
1Text
2 Handmade soy candle, 8 oz 5
30

How it works

TRIM(A2)
Removes leading/trailing spaces and collapses runs of spaces to one.
LEN(SUBSTITUTE(A2," ",""))
Length with every space removed.
+1
Words = spaces between words + 1.
IF(LEN(TRIM(A2))=0,0,…)
Returns 0 for empty or space-only cells instead of 1.

Common errors and fixes

Count is 1 for an empty cell?

You left out the IF wrapper.

Count too high?

Text has non-breaking spaces (CHAR(160)) from the web. SUBSTITUTE them first: see TRIM/CLEAN.

Does this work in Google Sheets?

Yes. The formulas on this page work the same way in Google Sheets.