# 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

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn