counting number of word in a cell

  • Thread starter Thread starter kokhong
  • Start date Start date
K

kokhong

is there a function can count number of word in a cell

example:

A B
username count
kok, hong, Lee
chan, NG
Ti, Lee

after counting
A B
username count
kok, hong, Lee 3
chan, NG 2
Ti, Lee 2
 
You can count the number of spaces in the cell then add 1:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
 
this problem i have settled..this can be found form MIscrosoft help..
the function is :
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
 
Using Trim is a very good idea because, accidently, there might have been
typed more than one space between two words.
Micky
 
One more that will work ok if the cell is empty:

If the words are separated by spaces:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")
 
Back
Top