Page 1 of 2 12 LastLast
Results 1 to 10 of 18
Like Tree1Likes

how to count spaces in a text string

This is a discussion on how to count spaces in a text string within the Excel Questions forums, part of the Question Forums category; I am looking for this for some time. Is there any formula (not vba ) to count that how many ...

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Posts
    86

    Default how to count spaces in a text string

    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.
    Last edited by loverfellow; Jul 10th, 2008 at 02:19 AM.

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,897

    Default Re: how to count spaces in a text string

    Hi, Something like this, maybe. Regards, Fazza

    Code:
    =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
    richardh9935 likes this.

  3. #3
    Board Regular
    Join Date
    Mar 2008
    Posts
    86

    Default Re: how to count spaces in a text string

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

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,897

    Default Re: how to count spaces in a text string

    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?

  5. #5
    Banned
    Join Date
    Sep 2006
    Location
    Bombay, India
    Posts
    3,274

    Default Re: how to count spaces in a text string

    Quote Originally Posted by loverfellow View Post
    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.

  6. #6
    Board Regular
    Join Date
    Mar 2008
    Posts
    86

    Default Re: how to count spaces in a text string

    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?

  7. #7
    Banned
    Join Date
    Sep 2006
    Location
    Bombay, India
    Posts
    3,274

    Default Re: how to count spaces in a text string

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

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,897

    Default Re: how to count spaces in a text string

    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.

  9. #9
    Banned
    Join Date
    Sep 2006
    Location
    Bombay, India
    Posts
    3,274

    Default Re: how to count spaces in a text string

    Quote Originally Posted by Fazza View Post
    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 !

  10. #10
    Board Regular
    Join Date
    Mar 2008
    Posts
    86

    Default Re: how to count spaces in a text string

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com