If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|