number of spaces...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mark - 17 Oct 2006 16:58 GMT

Hello.

I know someone showed me this before, but I've forgotten.

If I wanted to do a count of the number of spaces within a single cell, how
could I do that?

For instance, if I wanted to look at a cell that contained 'A B C D', and
get the answer that there are three spaces in that.

Thanks.
Mark

Reply to this Message

Bernard Liengme - 17 Oct 2006 17:07 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hello.
>
[quoted text clipped - 9 lines]
> Thanks.
> Mark

Reply to this Message

Dave Peterson - 17 Oct 2006 17:11 GMT

=(len(a1)-len(substitute(a1," ","")))/len(" ")

Since you're only counting a single character, you could eliminate the
denominator:

=len(a1)-len(substitute(a1," ",""))

> Hello.
>
[quoted text clipped - 8 lines]
> Thanks.
> Mark

Signature

Dave Peterson

Reply to this Message