How To Count The Number Of Words In A Cell

2017-08-19

Example

=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 freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest