count

[mohadin]

Hi all
Q: can a formula to count the words in a cell or count the spaces in that cell
If cell A1 contain for example "chandoo is the best forums"
In cell b2 should have 5 as words count or 3 as spaces count
Thanks

 

[Naresh Yadav]

pls use this one to get the count of space
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
and if there is no extra space at the beginning,end and middle of your data range you can simply add (your count of space result+1)
pls find the attached file for your clear understanding..

 

[bosco_yip]

1] Count the number of spaces

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

or,

this array formula, confirm enter with SHIFT+CTRL+ENTER instead of just ENTER.

=COUNT(0/(MID(A2,ROW($1:$99),1)=" "))

2] Count the number of words

Use the above space nos formula result + 1

Regards
Bosco

 

[mohadin]

pls use this one to get the count of space
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
and if there is no extra space at the beginning,end and middle of your data range you can simply add (your count of space result+1)
pls find the attached file for your clear understanding..

Thank you very much
PS.
I think we can over come the extra spaces by trim the cell
is that correct?

 

[mohadin]

1] Count the number of spaces

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

or,

this array formula, confirm enter with SHIFT+CTRL+ENTER instead of just ENTER.

=COUNT(0/(MID(A2,ROW($1:$99),1)=" "))

2] Count the number of words

Use the above space nos formula result + 1

Regards
Bosco

That great sir
Thank you in deed