#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default Cell validation

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Cell validation

=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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Cell validation

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






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default Cell validation

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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default Cell validation

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Cell validation

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




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Cell validation

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


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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




  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default Cell validation


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








  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default Cell validation


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





  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

The final =10 at the end of the formula controls the length. There are 10
logical expressions being added together; hence, if all true, then they will
total 10 and that total will be compared to the final =10. That means the
whole formula will evaluate to TRUE only if all 10 logical expressions are
true... and they will all be true only if each character meets the test
prescribed for them. Try it out and see.

Rick


"Sai Krishna" wrote in message
...

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









  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Cell validation

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


  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Cell validation

=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









  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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










  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Cell validation

=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














  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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














  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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















  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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

















  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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






  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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




















  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

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






  #27   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

Those INDIRECTs make it robust against row insertions.

If rows will *never* be inserted then, yes, we can do without them.

--
Biff
Microsoft Excel MVP


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








  #28   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Cell validation

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




















  #29   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell validation

True, but they also make the formula volatile as well.

Rick


"T. Valko" wrote in message
...
Those INDIRECTs make it robust against row insertions.

If rows will *never* be inserted then, yes, we can do without them.

--
Biff
Microsoft Excel MVP


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









  #30   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Cell validation

hi,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As String, res As String, i As Integer
Set rng = Range("A1:A10") 'Adapt this range as your wish
Set isect = Application.Intersect(Target, rng)
txt = Array( _
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", _
"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", _
"", "à", "é", "è") ' you can add characters the way you want

If Not isect Is Nothing Then
For i = 1 To Len(Target)
c = Mid(Target, i, 1)
If IsError(Application.Match(c, txt, 0)) Then res = res & Mid(Target, i, 1) & ", "
Next
If Len(Target) 2 And Len(Target) < 35 Then
If res = "" Then
Exit Sub
Else
MsgBox "The following characters are forbidden : " & res & Chr(10) & _
"Please start again" & Chr(10) & _
"Don't forget : only alphabets of length 3 to 35"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If
Else
MsgBox "Don't forget : only alphabets of length 3 to 35"
End If
End If
End Sub



--
isabelle



Le 2012-01-06 04:49, KRISHNA a écrit :
Hi,
Would need a macro to restrict a set of cells with only alphabets of length 3 to 35

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation Data using Validation Table cell range..... Dermot Excel Discussion (Misc queries) 16 January 5th 10 10:35 PM
Cell Validation Richhall[_2_] Excel Worksheet Functions 2 January 2nd 08 11:34 AM
In Cell Validation List & Linked Cell VLOOKUP BEEZ Excel Discussion (Misc queries) 7 July 1st 07 07:17 PM
Data Validation: Store cell address instead of value in the cell? WillW Excel Discussion (Misc queries) 1 January 31st 07 03:22 AM
Cell Validation Simon Shaw Excel Discussion (Misc queries) 5 March 4th 05 12:51 AM


All times are GMT +1. The time now is 03:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"