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

=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

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

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

Hi Bob,

thanks a lot. It has worked!!!
regards
sai

"Bob Phillips" wrote:

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

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

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

The OP seems to be satisfied but....

The cell will accept:

<space~!<space&1234)

--
Biff
Microsoft Excel MVP


"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

In this example the AAAAI7504G type code is contained in C1:

=AND(MID(C1,1,1)="A",MID(C1,2,1)="A",MID(C1,3,1) ="A",MID(C1,4,1)="A",MID(C1,5,1)="A",MID(C1,10, 1)="A",MID(C1,1,1)<="Z",MID(C1,2,1)<="Z",MID(C1,3 ,1)<="Z",MID(C1,4,1)<="Z",MID(C1,5,1)<="Z",MID(C1, 10,1)<="Z",ISNUMBER(VALUE(MID(C1,6,4))),LEN(C1)=10 )

Regards,
Stefi

€žSai Krishna€� ezt Ã*rta:

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

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

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

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

That will accept more than 10 characters.

Slightly shorter but ugly as all get-out:

=SUM(--(LEN(A1)=10),5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4),--AND(CODE(RIGHT(A1))64,CODE(RIGHT(A1))<91))=8

If you enter this as a worksheet function it has to be array entered.

Meets these conditions:

Length must be 10 characters
The first 5 characters must be uppercase letters A-Z
Characters 6:9 must be numbers 0-9
The last character must be uppercase letters A-Z

--
Biff
Microsoft Excel MVP


"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

Slightly shorter with 2 less function calls:

=SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90 "))),"")),COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4,LOOKUP(CODE(RIGH T(A1)),{65,90,91},{1,1,0}))=8

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
That will accept more than 10 characters.

Slightly shorter but ugly as all get-out:

=SUM(--(LEN(A1)=10),5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4),--AND(CODE(RIGHT(A1))64,CODE(RIGHT(A1))<91))=8

If you enter this as a worksheet function it has to be array entered.

Meets these conditions:

Length must be 10 characters
The first 5 characters must be uppercase letters A-Z
Characters 6:9 must be numbers 0-9
The last character must be uppercase letters A-Z

--
Biff
Microsoft Excel MVP


"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

I'm not 100% sure, but I think you can remove those INDIRECT function
calls...

=SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(65:90)),"")),CO UNT(-MID(A1,ROW(6:9),1))=4,LOOKUP(CODE(RIGHT(A1)),{65,9 0,91},{1,1,0}))=8

Giving it a quick test, it looks like this version of your formula
(array-entered, of course) works.

Rick


"T. Valko" wrote in message
...
Slightly shorter with 2 less function calls:

=SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90 "))),"")),COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4,LOOKUP(CODE(RIGH T(A1)),{65,90,91},{1,1,0}))=8

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
That will accept more than 10 characters.

Slightly shorter but ugly as all get-out:

=SUM(--(LEN(A1)=10),5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4),--AND(CODE(RIGHT(A1))64,CODE(RIGHT(A1))<91))=8

If you enter this as a worksheet function it has to be array entered.

Meets these conditions:

Length must be 10 characters
The first 5 characters must be uppercase letters A-Z
Characters 6:9 must be numbers 0-9
The last character must be uppercase letters A-Z

--
Biff
Microsoft Excel MVP


"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

Thanks for answer.

Formula restricting the length to 10 characters would be great.
regards
krishna

"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

Just add a multiplication with (LEN(A2)=10)

=(LEN(A2)=10)*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 23, 10:27 am, Sai Krishna
wrote:
Thanks for answer.

Formula restricting the length to 10 characters would be great.
regards
krishna

"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