Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Cell Validation | Excel Worksheet Functions | |||
In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
Data Validation: Store cell address instead of value in the cell? | Excel Discussion (Misc queries) | |||
Cell Validation | Excel Discussion (Misc queries) |