Count Total Words in a Cell – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 26, 2023
Download Example Workbook

Download the example workbook

This tutorial will teach you how to count words in a cell in Excel and Google Sheets.

Count Total Words in Cells Main

Counting Words

There is no built-in Excel function to count the number of words in a cell. However, we can count the number of spaces, which can tell us how many words are in a cell:

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

count words in cell EX 01

Let’s see how this formula works.

Remove All Spaces

First, we use SUBSTITUTE Function to remove all spaces from the text string.

=SUBSTITUTE(B3," ","")

count words in cell EX 02

Then we determine the length of this space-free string with the LEN Function.

=LEN(C3) or =LEN(SUBSTITUTE(B3," ",""))

count words in cell EX 03

We will compare the string length before and after removing the spaces, to count how many spaces the cell contains.

Remove Extra Spaces

Next, we want to express this string as we normally would (i.e. containing only single spaces between words). The TRIM function removes extra spaces from a text string, leaving a single spaces between each word, so all leading, trailing, and repeated spaces will be excluded.

=TRIM(B3)

count words in cell EX 04

Then we determine the length of this normally-arranged text with the LEN function.

=LEN(E3) or =LEN(TRIM(B3))

count words in cell EX 05

Finding the different between the two lengths (with and without spaces) and adding one (since there is no space after the last word) gives us the word count of the cell:

=F3-D3+1

count words in cell EX 06

Combining these steps gives us the formula:

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

count words in cell EX 01

 

Google Sheets –Count Total Words in a Cell

All of the above examples work exactly the same in Google Sheets as in Excel.

count-words-in-cell-Google-Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List