Results 1 to 7 of 7

Counting Spaces?

This is a discussion on Counting Spaces? within the Excel Questions forums, part of the Question Forums category; Does anyone know of an Excel function, or more specifically, a series of excel functions, that can be used to ...

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Location
    Long Island, NY
    Posts
    81

    Default Counting Spaces?

    Does anyone know of an Excel function, or more specifically, a series of excel functions, that can be used to count spaces, or any character or string I specify.

    I'm not really interested in using VBA to accomplish this, though I understand I could easily solve my problem this way. I want to exhaust the inherent excel functions first.

    Thanks,

    Rob

  2. #2
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    West Bridgford, Notts., England
    Posts
    2,834

    Default Re: Counting Spaces?

    Search the xl help on "countblank"

    HTH
    Iridium


  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,988

    Default Re: Counting Spaces?

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

    would give you a count of spaces in the string in A1.

  4. #4
    Board Regular
    Join Date
    Sep 2003
    Location
    Long Island, NY
    Posts
    81

    Default Re: Counting Spaces?

    The Count functions, including countblank, countif, counta, and count, perform tasks on ranges of cells, not parsing cell contents, unless I'm missing something.

    To clarify, I am looking for an easier way to do this than finding a space with "find", counting it, then finding the next space, and so on.

    I realize there is probably not something that will help me, except to write out the tedious "find" functions. If not, I nominate "countcell" as a new function for the next version of excel, one that counts the number of instances of X text in an individual cell.

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Location
    Long Island, NY
    Posts
    81

    Default Re: Counting Spaces?

    Aladin, that's brilliant. I'm so stupid.

  6. #6
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    West Bridgford, Notts., England
    Posts
    2,834

    Default Re: Counting Spaces?

    No you're not missing anything - I misread your post so as you say COUNTBLANK won't give you what you're after - sorry!
    Iridium


  7. #7
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,160

    Default Re: Counting Spaces?

    I agree with dabluebery, this is pretty cool.
    Rather than count the "spaces" between words it seems to count the number of times the spacebar was actually used. (For example if you double spaced the words "What a cool formula" you get a return of 6 instead of 3.)
    Good one Aladin.

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