On Jan 28, 4:41*am, Harlan Grove wrote:
"Rick Rothstein" wrote...
My guess is there should be a simpler Custom validation formula than
the following one; but, until someone posts it, this will have to
do<g....

=AND(OR(LEN(A1)=3,LEN(A1)=4),ISNUMBER(--LEFT(A1,3)),
ISNUMBER(SEARCH(MID(A1,4,1),"abcdefghijklmnopqrst uvwxyz")))

...

If A1 contained the text "-.0", that'd produce a TRUE result from your
first ISNUMBER call. You want to test that the first 3 chars in A1 are
NUMERALS *NOT* that they're numeric. There's a difference.

And if the 4th char in A1 were either ? or *, the second ISNUMBER call
would also produce a TRUE result. SEARCH isn't reliable when it's
first argument would be a user entry.

Maybe there's something shorter, but the following exactly satisfies
the OP's specs.

=AND(LEN(A1)<5,LEFT(A1,3)=TEXT(--LEFT(A1,3),"000;;000;"),
NOT(EXACT(LOWER(MID(A1&"z",4,1)),UPPER(MID(A1&"z", 4,1)))))


thank you
but i did not get answer i require,

sometime i need to enter just the numbers rather than number and
string which this data validation does not allow.

please update me on this