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