How To Count The Number Of Words In A Cell


=LEN(B3)-LEN(SUBSTITUTE(B3,” “,””))+1

Generic Formula

=LEN(Sentence)-LEN(SUBSTITUTE(Sentence,” “,””))+1

Sentence – This is the cell containing the sentence for which you want to count the number of words it contains.

What It Does

This formula will count the number of words contained in a given cell.

How It Works

This formula works based on the words being separated by exactly one space character. It counts the number of space characters contained in the cell and then adds 1 to this count. Generally, if we have N spaces then we should have N+1 words.

The formula counts the number of space characters by taking the difference between these two items:

  • Character count of the full text string. This is given by the LEN(Sentence) part of the formula.
  • Character count of the text string with all the space characters removed. This is given by the LEN(SUBSTITUTE(Sentence,” “,””)) part of the formula.

In our example LEN(“The quick brown fox jumps over the lazy dog”) results in 43 since there are 43 characters in the string.

In our example SUBSTITUTE(“The quick brown fox jumps over the lazy dog”,” “,””) results in the text string “Thequickbrownfoxjumpsoverthelazydog”, where each occurrence of a space has been removed. The character count of this new text string is then found by LEN(“Thequickbrownfoxjumpsoverthelazydog”) and this results in 35 since there are 35 characters in this string.

The difference of LEN(Sentence)-LEN(SUBSTITUTE(Sentence,” “,””)) is just the count of all our removed space characters from the text string. In our example this is 43-35, so there are 8 space characters in the text string, which means there are 8+1 = 9 words.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.


Related Posts



Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃