counting number of word in a cell
 

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

counting number of word in a cell
 

You can count the number of spaces in the cell then add 1:

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

--
Biff
Microsoft Excel MVP


"kokhong" wrote in message
...
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

counting number of word in a cell
 

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)

"kokhong" wrote:

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

counting number of word in a cell
 

Using Trim is a very good idea because, accidently, there might have been
typed more than one space between two words.
Micky


"kokhong" wrote:

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)

"kokhong" wrote:

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

counting number of word in a cell
 

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)<"")

kokhong wrote:

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

--

Dave Peterson