software

Is is possible to determine the number of blank characters entered in a
cell? What I mean by Blank, is when the user uses the spacebar to clear the
contents of the cell

I have used =LEN but that returns a value for all characters blank and
otherwise.Thanks
=LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Johnquot; gt; wrote in message
...
gt; Is is possible to determine the number of blank characters entered in a
gt; cell? What I mean by Blank, is when the user uses the spacebar to clear
the
gt; contents of the cell
gt;
gt; I have used =LEN but that returns a value for all characters blank and
gt; otherwise.
gt;
gt;
gt; Thanks
gt;
gt;
Thanks Bob, very very cleverquot;Bob Phillipsquot; gt; wrote in message
...
gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;Johnquot; gt; wrote in message
gt; ...
gt;gt; Is is possible to determine the number of blank characters entered in a
gt;gt; cell? What I mean by Blank, is when the user uses the spacebar to clear
gt; the
gt;gt; contents of the cell
gt;gt;
gt;gt; I have used =LEN but that returns a value for all characters blank and
gt;gt; otherwise.
gt;gt;
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt;
gt;
gt;
One twist on this Bob, is that, is it possible to just count the Blanks to
the left of the first word. In my cell two words entered are valid but that
means a blank between the first and second, so I would have to allow that.
My idea is that when I get a value gt;0 then I delete the contents of A1, but
if two words are typed in that would return 1 and in that case I would
incorrectly delete. Thus blanks before the first word are the entries that
are invalidquot;Johnquot; gt; wrote in message
...
gt; Thanks Bob, very very clever
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt;gt; =LEN(A1)-LEN(SUBSTITUTE(A1,quot; quot;,quot;quot;))
gt;gt;
gt;gt; --
gt;gt;
gt;gt; HTH
gt;gt;
gt;gt; RP
gt;gt; (remove nothere from the email address if mailing direct)
gt;gt;
gt;gt;
gt;gt; quot;Johnquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Is is possible to determine the number of blank characters entered in a
gt;gt;gt; cell? What I mean by Blank, is when the user uses the spacebar to clear
gt;gt; the
gt;gt;gt; contents of the cell
gt;gt;gt;
gt;gt;gt; I have used =LEN but that returns a value for all characters blank and
gt;gt;gt; otherwise.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Thanks
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
quot;Johnquot; gt; wrote...
gt;One twist on this Bob, is that, is it possible to just count the Blanks to
gt;the left of the first word. In my cell two words entered are valid but that
gt;means a blank between the first and second, so I would have to allow that.
gt;My idea is that when I get a value gt;0 then I delete the contents of A1, but
gt;if two words are typed in that would return 1 and in that case I would
gt;incorrectly delete. Thus blanks before the first word are the entries that
gt;are invalid
....

If you want to eliminate leading spaces and would also accept eliminating
any trailing spaces and compressing any sequences of multiple spaces between
words into single spaces, use the TRIM function.

If you really want just the number of leading spaces, try

=FIND(LEFT(TRIM(A1),1),A1)-1
Thanks Harlan, I can work with thatquot;Harlan Grovequot; gt; wrote in message
...
gt; quot;Johnquot; gt; wrote...
gt;gt;One twist on this Bob, is that, is it possible to just count the Blanks to
gt;gt;the left of the first word. In my cell two words entered are valid but
gt;gt;that means a blank between the first and second, so I would have to allow
gt;gt;that. My idea is that when I get a value gt;0 then I delete the contents of
gt;gt;A1, but if two words are typed in that would return 1 and in that case I
gt;gt;would incorrectly delete. Thus blanks before the first word are the
gt;gt;entries that are invalid
gt; ...
gt;
gt; If you want to eliminate leading spaces and would also accept eliminating
gt; any trailing spaces and compressing any sequences of multiple spaces
gt; between words into single spaces, use the TRIM function.
gt;
gt; If you really want just the number of leading spaces, try
gt;
gt; =FIND(LEFT(TRIM(A1),1),A1)-1
gt;