shashidhar wrote...
On Jan 28, 4:41 am, Harlan Grove wrote:
....
=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.

So your entries could be plain numbers? If so, your original example
of 001 was misleading. Does the entry cell have the number format 000
through which it display 001 when you enter 1? If so, the validation
formula would need to be

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

This allows entry of whole numbers between 0 and 999, strings of 3
decimal numerals possibly followed by a single Western European
letter. That is, it'll accept accented letters. If you want only
unaccented/English letters, try

=OR(A1=INT(MOD(ABS(N(A1)),1000)),AND(LEN(A1)<5,
LEFT(A1,3)=TEXT(--LEFT(N(A1),3),"000;;000;"),
ABS(CODE(UPPER(MID(A1&"z",4,1)))-78.5)<13))