do you think the formula works shape-wise?

Yeah, it works if case is not a consideration and rows will not be inserted.



--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I allowed for that on purpose since the OP didn't specifically say to
ignore them (his original requirement was only for "alphanumeric"
characters). On top of that, the second formula that Bob posted, a
modification of his original formula in response to a comment by me, and
which the OP indicated "worked" for him, allowed for both upper and lower
case letters... I figured that was a confirmation that upper/lower case
letters were both acceptable. We will have to wait for the OP to come back
to this thread and address this issue before we can know for sure what his
intention was. So, putting that issue aside, do you think the formula works
shape-wise? Or did I miss something?

Rick


"T. Valko" wrote in message
...
Accepts lower case letters:

aBcDe9999F
aaaaa0000a

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
I ***think*** this array-entered formula might work...

=AND(AND(MID(A1,ROW($1:$5),1)="A",MID(A1,ROW($1:$ 5),1)<="Z"),AND(MID(A1,ROW($6:$9),1)="0",MID(A1,R OW($6:$9),1)<="9"),AND(MID(A1,10,1)="A",MID(A1,10 ,1)<="Z"),LEN(A1)=10)

Note for the archives: Commit this formula with Ctrl+Shift+Enter instead
of just Enter by itself.

Rick


"T. Valko" wrote in message
...
Still chokes:

?A?c*0000?
*****1234*
**a??0000x


--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
=AND(LEN(A1)=10,SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CH AR(ROW(INDIRECT("65:90"))),0)))))=0,
SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("6:9")),1))))=4,ISNUMBER(MATCH (UPPER(RIGHT(A1,1)),CHAR(ROW(INDIRECT("65:90"))),0 )))

there you go.

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)"
wrote in message ...
You will need to add something for the length... ABCDE1234FABCDEF
evaluates to TRUE.

Rick


"Bob Phillips" wrote in message
...
=AND(SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CH AR(ROW(INDIRECT("65:90"))),0)))))=0,SUMPRODUCT(--(ISNUMBER(--MID(A1,ROW(INDIRECT("6:9")),1))))=4,ISNUMBER(MATCH (UPPER(RIGHT(A1,1)),CHAR(ROW(INDIRECT("65:90"))),0 )))

an array formula.

Before anyone pulls me up about ROW($1:$5) (as if!), I included it
as ROE(INDIRECT exceeds 7 nested functions.

--
---
HTH

Bob


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



"Rick Rothstein (MVP - VB)"
wrote in message ...
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