The ISNUMBER test appears to return 1 (TRUE) for floating point values, such
as this...

ABCDE1.34F

Rick


"vezerid" wrote in message
...
Correction for the ISNUMBER part, MID(A2,6,4) is needed:

=PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")),
1))64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91)) *ISNUMBER(--
MID(A2,6,4))*(CODE(RIGHT(A2,1))64)*(CODE(RIGHT(A2 ,1))<91)

Still an array formula.
I tried to simplify the check for a number, although this will accept
parts in positions 6-9 like:
ABCDE-123G
ABCDE1E10G

If we want to guard against these possibilities then the more complex
formula is warranted:

=PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")),
1))64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91)) *PRODUCT(--
ISNUMBER(--MID(A2,ROW(INDIRECT("6:9")),
1)))*(CODE(RIGHT(A2,1))64)*(CODE(RIGHT(A2,1))<91)

HTH
Kostis

On May 22, 7:16 pm, vezerid wrote:
The following formula will accept only capital letters of the alphabet
in positions 1-5, only a number in positions 6-9 and only a capital
letter in position 10. It is an array formula, hence commit with Shift
+Ctrl+Enter

=PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")),
1))64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91)) *ISNUMBER(--
MID(A2,6,3))*(CODE(RIGHT(A2,1))64)*(CODE(RIGHT(A2 ,1))<91)

HTH
Kostis Vezerides

On May 22, 7:57 am, Sai Krishna
wrote:

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