Count Number or words in Cell

[samcannet1]

Is there a way to count the number of words in just a cell? If so,
how?

Thanks
Samantha

 

[Pete_UK]

If the words are separated by a single space then you can compare the
length of the cell with the length of the cell after spaces have been
removed and add one. Like this:

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

If you had "Mary had a lamb" in A1, then this formula will return 4.

Hope this helps.

Pete

 

[PCLIVE]

With the assumption that each word is separated by only one (1) space with
no spaces at the beginning or at the end, then you could use the following
formula.

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

HTH,
Paul

 

[Pete_UK]

Paul,

use TRIM to try to enforce your assumptions.

Pete

 

[PCLIVE]

Thanks Pete. I should have thought of that.

--

Paul,

use TRIM to try to enforce your assumptions.

Pete

 

[Ron Rosenfeld]

If the words are separated by a single space then you can compare the
length of the cell with the length of the cell after spaces have been
removed and add one. Like this:

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

If you had "Mary had a lamb" in A1, then this formula will return 4.

Hope this helps.

Pete

And your formula would also return 1 with a blank cell. You need to test for
it.
--ron

 

[Dave Peterson]

But to avoid returning 1 when the cell is empty:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

 

[Pete_UK]

Thanks for pointing that out, Ron. I see that Dave has come up with a
way of coping with an empty cell.

Pete