How to Count Words in a Cell in Excel

Count words by counting spaces — with TRIM to handle messy spacing — and total the words across a range for per-word pricing.

You will learn to

• Count words with the LEN/SUBSTITUTE trick
• Make the count robust to extra spaces
• Total word counts across a range

How it works

Excel has no built-in word counter, but words are separated by spaces, so: number of words = number of spaces + 1. Count the spaces by comparing the length with and without them: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. TRIM matters, because double or trailing spaces would otherwise be counted as extra words.

To count words across a whole column, put the same expression inside SUMPRODUCT, which handles ranges: =SUMPRODUCT(LEN(TRIM(B2:B6))-LEN(SUBSTITUTE(TRIM(B2:B6)," ",""))+1). In Excel 365 you can also use =COUNTA(TEXTSPLIT(TRIM(A2)," ")).

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