Hi rick,

this has also worked. The formula gives an impression that it would
restrict the text length to 10. But it is not restricting the text length to
10. Ok, we could use the text length under data validation. But if the
formula could take care, it would be wonderful.

Thanks for your efforts.
regards
krishna
"Rick Rothstein (MVP - VB)" wrote:

I'm beginning to think the only "foolproof" way to do this might be this
formula...

=AND(MID(A1,1,1)="A",MID(A1,1,1)<="Z")+AND(MID(A1 ,2,1)="A",MID(A1,2,1)<="Z")+AND(MID(A1,3,1)="A", MID(A1,3,1)<="Z")+AND(MID(A1,4,1)="A",MID(A1,4,1) <="Z")+AND(MID(A1,5,1)="A",MID(A1,5,1)<="Z")+AND( MID(A1,6,1)="0",MID(A1,6,1)<="9")+AND(MID(A1,7,1) ="0",MID(A1,7,1)<="9")+AND(MID(A1,8,1)="0",MID(A 1,8,1)<="9")+AND(MID(A1,9,1)="0",MID(A1,9,1)<="9" )+AND(MID(A1,10,1)="A",MID(A1,10,1)<="Z")=10

although I do note some ALT+0### "type-able" keyboard characters (for
example, ALT+0140, ALT+0153, and others) will pass the test; however, it
would be foolproof for the "normal" keyboard characters.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm sorry, but it turns out there is still a problem... none of the
"letter" entry characters need to actually be letters to pass through your
formula. Here is one of the several worst possibilities that your formula
will return TRUE for...

$+#%&1234*

Rick


"Bob Phillips" wrote in message
...
easily resolved

=AND(SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("1:5")),1))))=0,SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("6:9")),1))))=4,NOT(ISNUMBER(--RIGHT(A21,1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rick Rothstein (MVP - VB)" wrote
in message ...
That formula is not foolproof; for example, consider these entries...

ABCDE12.3F
ABCDE+123F
ABCDE$123F
ABCDE(12)F

Rick


"Bob Phillips" wrote in message
...
=AND(SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("1:5")),1))))=0,ISNUMBER(--MID(A21,6,4)),NOT(ISNUMBER(--RIGHT(A21,1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sai Krishna" wrote in message
...
Hi,

I have a cell where in I need to enter an alphanumeric strictly as
under

AAAAI7504G

The rule is : First 5 should be alphabets, next 4 should be numbers
and the
last again an alphabet.

The total characters are therefore 10. For the length of the
character, we
can use Text length under Data validation. But we need to ensure that
rule is
followed.

The above is only an illustration. But the positions of alphabets and
numbers should be as per the rule above.

Now is there any formula that throws up a warning saying that a number
has
been entered in place of an alphabet. In other words, the above rule
should
not be violated.

regards
krishna