# 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. ## 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. ## Re: Counting Spaces?

Search the xl help on "countblank"

HTH

3. ## Re: Counting Spaces?

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

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

4. ## 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. ## Re: Counting Spaces?

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

6. ## 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!

7. ## 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.)

8. ## Re: Counting Spaces?

10 years later and still helping! Thanks

9. ## Re: Counting Spaces?

Select the range to count, go to Edit/Replace, replace a space with a space, click Replace All.

10. ## Re: Counting Spaces?

Originally Posted by HalfAce
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.)
Digging up an old thread. Is there a formula to count the number of spaces between each word in a string?

Thanks!!

Page 1 of 3 123 Last

#### Posting Permissions

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