Hello,

I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells that
have more than 3 names.

Thanks in advance for your replies.

Phil.

Perhaps with something like this:

For a name in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Phil" wrote:

Hello,

I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells that
have more than 3 names.

Thanks in advance for your replies.

Phil.

You would need to count the spaces then, so any 3 and more names in a cell
would have at least 2 spaces

You could use a help column and a formula like

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=2


copy down, apply autofilter and filter on TRUE will give all those cells

or use conditional formatting formula is and the high light those cells

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Phil" wrote in message
...
Hello,

I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells
that
have more than 3 names.

Thanks in advance for your replies.

Phil.

Ron,

Thank you. That worked perfectly.

"Ron Coderre" wrote:

Perhaps with something like this:

For a name in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Phil" wrote:

Hello,

I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells that
have more than 3 names.

Thanks in advance for your replies.

Phil.