Count Number or words in Cell

  • Thread starter Thread starter samcannet1
  • Start date Start date
S

samcannet1

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
 
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
 
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
Click to expand...

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)<>"")
 
Thanks for pointing that out, Ron. I see that Dave has come up with a
way of coping with an empty cell.

Pete
 
Back
Top