Is it Possible

[John]

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

[Bob Phillips]

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[John]

Thanks Bob, very very clever


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

[John]

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 >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 invalid


"John" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob, very very clever
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>> =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "John" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>
>>
>
>

[Harlan Grove]

"John" <[email protected]> wrote...
>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 >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 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

[John]

Thanks Harlan, I can work with that


"Harlan Grove" <[email protected]> wrote in message
news:ODc0B0%[email protected]...
> "John" <[email protected]> wrote...
>>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 >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 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
>