Excel : Count the number of words in a cell

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

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

About Author:

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


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

Let's Get Connected: Email | LinkedIn

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