Excel : Count the number of words in a cell

We all know LEN function returns the number of characters in a text string but there is no such an inbuilt function to calculate the number of words in a cell . However we can calculate the number of words in a cell using a simple nesting formula.

A1 : I love excel like a love song

B1 : =LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

In simple words, the above formula means 
=LEN(I love excel like a love song) - LEN(Iloveexcellikealovesong) + 1

Note : Here the trick to count the number of spaces within text .

STEP I : 

= LEN(TRIM(A1))

To count the number of characters . Trim is used to eliminate the leading and trailing spaces. The return value is 29 .

STEP II :

= SUBSTITUTE(A1," ","")

To remove space delimiter in a cell . In the above example , return value is Iloveexcellikealovesong

STEP III :

=LEN(SUBSTITUTE(A1," ",""))+1

To count the number of characters in " Iloveexcellikealovesong " string. The answer is 23

STEP IV :

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1 .

=LEN(I love excel like a love song) - LEN(Iloveexcellikealovesong) + 1

i.e. 29-23+1 =7 .

Tip : If words are separated by a comma in a cell you can modify the above formula with substituting space with comma. =LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,",",""))+1

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Excel : Count the number of words in a cell"

Post a Comment

Next → ← Prev