Count number of words in a cell in Excel and Google Sheets

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

A2 = data cell

Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.

Count the number of words in a cell in Excel

How to count the number of words in a cell in Excel?
Count the number of words in a cell in Excel

COUNT NUMBER OF WORDS IN A CELL — EXCEL FORMULA AND EXAMPLE

  1. =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

  2. =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

Count the number of words in a cell in Google Sheets

How to count the number of words in a cell in Google Sheets?
Count the number of words in a cell in Google Sheets

COUNT NUMBER OF WORDS IN A CELL — GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

  2. =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

Other useful "COUNT FUNCTIONS: TEXT BASED CRITERIA" formulas in Excel and Google SheetsOthers FormulasDate and time functionsNumber based criteriaExtract functions