Excel Tips - How to Count Total Words In a Cell

Count total words in a cell

=LEN(a1)-LEN(SUBSTITUTE(a1," ",""))+1

If you want to count the total words in a cell, you can do so with a formula that uses the LEN and SUBSTITUTE functions. In the generic form above, a1 represents the address of the cell  containing words being counted.

In the example, we are using this formula:

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

Here's how the formula works:

SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of spaces + 1.

What about TRIM, what's it doing in the formula? TRIM removes an extra spaces between words, or at the beginning or end of the text. So, before the length of the text with spaces is calculated, we use TRIM to make sure the text doesn't contain any extra spaces, since extra spaces will throw the word count off.

Note that this formula will return 1 even if a cell is empty. If you need to guard against this problem, using IF and ISBLANK:

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

 VIA EXCELJET

Share on Google Plus

About Salman Ahmed Rajput

Hi I am Salman Ahmed, nicknamed as "Sam". I am a technology lover and also a Web Developer. My favorite programming language is PHP which means I usually develop web apps in PHP. Wanna Follow ? Yes? You can follow me at https://facebook.com/Sam.programer
https://twitter.com/SalmanRajz.