Search the xl help on "countblank"
HTH
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 ...
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
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
would give you a count of spaces in the string in A1.
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.
Aladin, that's brilliant. I'm so stupid.
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.
10 years later and still helping! Thanks
Select the range to count, go to Edit/Replace, replace a space with a space, click Replace All.
Like this thread? Share it with others