Count Total Words in a Cell – Excel, VBA & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on March 20, 2024
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:


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.


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:


count words in cell EX 06

Combining these steps gives us the formula:


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 Number of Words using VBA

By using a user-defined function within VBA, you can count the number of words in a cell with a single function.

The following VBA function counts the number of words in a string by searching for spaces within the string. It uses the MID Function. You could accomplish something similar with the SEARCH or FIND Functions.

Function Number_of_Words(Text_String As String) As Integer
'Function counts the number of words in a string
'by looking at each character and seeing whether it is a space or not
Number_of_Words = 0
Dim String_Length As Integer
Dim Current_Character As Integer

String_Length = Len(Text_String)

For Current_Character = 1 To String_Length

If (Mid(Text_String, Current_Character, 1)) = " " Then
    Number_of_Words = Number_of_Words + 1
End If

Next Current_Character
End Function

It is worth noting a couple of points:
• This code must be inserted in a Workbook Module – from the VBA pane, click on Insert and then module

201 no of words in a string 400x241

• The function can be called from a range in Excel:
=Number_of_Words(“Pig Dog Cat”)

The function works by traversing the length of a string and seeing if the next character is a space and if so it adds one to the number of spaces in the string.

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