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
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
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
Share Share Tweet