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.

 

[Fazza]

Hi, Something like this, maybe. Regards, Fazza

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

 

[loverfellow]

no, this don't count multiple spaces between words. Could there be any other formula.

 

[Fazza]

There are often many ways.

It sounds like we have different definitions of a space? The formula I posted counted spaces in the text string, and yes than can be more than one between the words. Every space (character) is counted.

What is your definition?

 

[Stormseed]

no, this don't count multiple spaces between words. Could there be any other formula

Did you apply this formula the way it shown here ? It is not possible the formula will not count multiple spaces in between words. It will infact all the spaces in the text string.

 

[loverfellow]

Oh, you are right, it counts all the spaces. But I have a text in a worksheet, only where it didn't apply. There are obviously 9 spaces in that text but the formula counts only 4. I believe those spaces were not entered in a normal way. Anyone have any idea about this?

 

[Stormseed]

Can you post the text as it is displayed on your worksheet ?

 

[Fazza]

What do you want to do?

Maybe apply a CLEAN function to remove them?

To investigate the characters, isolate them individually using the MID function - so put one character in one cell. And then use the CODE function to work out what the character is. You might also want to use the TRIM or LEN functions, too.

 

[Stormseed]

What do you want to do?

Maybe apply a CLEAN function to remove them?

To investigate the characters, isolate them individually using the MID function - so put one character in one cell. And then use the CODE function to work out what the character is. You might also want to use the TRIM or LEN functions, too

hi Fazza,

I think he is exporting the data (text strings) into Excel which is causing this to happen and I do agree that using CLEAN() he will be able to remove junk characters which may have become a part of these strings !

 

[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," ","")))