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

Thanks
Samantha

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


On Aug 15, 2:39 pm, wrote:
Is there a way to count the number of words in just a cell? If so,
how?

Thanks
Samantha

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

--

wrote in message
ups.com...
Is there a way to count the number of words in just a cell? If so,
how?

Thanks
Samantha

Paul,

use TRIM to try to enforce your assumptions.

Pete

On Aug 15, 2:49 pm, "PCLIVE" wrote:
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

--

wrote in message

ups.com...



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

Thanks
Samantha- Hide quoted text -

- Show quoted text -

Thanks Pete. I should have thought of that.

--

"Pete_UK" wrote in message
ps.com...
Paul,

use TRIM to try to enforce your assumptions.

Pete

On Aug 15, 2:49 pm, "PCLIVE" wrote:
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

--

wrote in message

ups.com...



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

Thanks
Samantha- Hide quoted text -

- Show quoted text -

On Wed, 15 Aug 2007 06:45:33 -0700, Pete_UK wrote:

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

But to avoid returning 1 when the cell is empty:

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

wrote:

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

Thanks
Samantha

--

Dave Peterson

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

Pete

On Aug 15, 3:06 pm, Ron Rosenfeld wrote:
On Wed, 15 Aug 2007 06:45:33 -0700, Pete_UK wrote:
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