modification of function

[Jim May]

The below code in Data Validation Custom (Compliments of Harland Grove):
If you really want nothing other than A-Za-z, try the rather longer & uglier

=AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
1)))<=90)

This WORKS GREAT!!;

Can it be modified to also accomodate space characters as well (code 32)
so that we can monitor the entry (in a single cell) of first, middle and
last names?

TIA,

 

[Dave Peterson]

I'm not sure if this is the simplest solution, but I think I'd just apply:

=substitute(a1," ","")
to eliminate any spaces from the string.
(either in a helper cell or directly in the formula)

=AND(MIN(CODE(MID(UPPER(SUBSTITUTE(A1," ","")),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))>=65,
MAX(CODE(MID(UPPER(SUBSTITUTE(A1," ","")),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))<=90)

still array entered.

 

[Guest]

Dave;
Thanks, but you misunderstood my request (I think);

"to eliminate any spaces from the string"

Actually I want to INCLUDE spaces if they are entered
into the cell, like: James M May
The revised function would verify that 9 characters I
entered qualified as a-z or A-Z AND would ALLOW for
the two spaces to be entered.
Tks,
Jim May

 

[Dave Peterson]

By replacing the spaces with nothing, the validation won't see those spaces--and
won't return a False.

When you tried it, did it work?

 

[Guest]

WOW, I just tried it ( I hadn't before you asked)!!
Sorry for questioning you (on this);
It does the trick!!
Thanks again,
Jim May

 

[Dave Peterson]

Glad it worked.

WOW, I just tried it ( I hadn't before you asked)!!
Sorry for questioning you (on this);
It does the trick!!
Thanks again,
Jim May