how to count spaces in a text string

[loverfellow]

I am looking for this for some time. Is there any formula (not vba) to count that how many occurances of spaces are in a text string, spaces could be more than one between the words.
Help me with this please.

 

[loverfellow]

thanks guys, the problem is solved. It was some kind of junk character which code is 160 while the code of the a normal space is 32. So, this was the text "Mf Offset Paper ", and I finally used this formula which worked fine.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

 

[loverfellow]

or this formula meant to work as well.
=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),""))+(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

 

[Stormseed]

Glad to know that you got your problem resolved

 

[pgc01]

Hi loverfellow

It was some kind of junk character which code is 160

For your information, it's the HTML non-breaking space. You usually get it when you copy text from the web.

 

[loverfellow]

thanks for the information

 

[Fazza]

Well done loverfellow for determinng & posting your solution.

 

[hema kangya]

I was just looking out for solution to the same problem.

Thanks! resolved.

 

[JHASDOIT]

I think this is the formula that you are looking for.

=SUM(--ISNUMBER(SEARCH(" ",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),1)))

press F2 and then ctrl+shift+enter.

i hope it could help