 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Cell 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 =AND(SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("1:5")),1))))=0,ISNUMBER(--MID(A21,6,4)),NOT(ISNUMBER(--RIGHT(A21,1))))  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 21 lines] > regards > krishna That formula is not foolproof; for example, consider these entries... ABCDE12.3F ABCDE+123F ABCDE$123F ABCDE(12)F Rick > =AND(SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("1:5")),1))))=0,ISNUMBER(--MID(A21,6,4)),NOT(ISNUMBER(--RIGHT(A21,1)))) > [quoted text clipped - 25 lines] >> regards >> krishna easily resolved =AND(SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("1:5")),1))))=0,SUMPRODUCT(--(ISNUMBER(--MID(A21,ROW(INDIRECT("6:9")),1))))=4,NOT(ISNUMBER(--RIGHT(A21,1)))) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > That formula is not foolproof; for example, consider these entries... > [quoted text clipped - 34 lines] >>> regards >>> krishna Hi Bob, thanks a lot. It has worked!!! regards sai > easily resolved > [quoted text clipped - 38 lines] > >>> regards > >>> krishna I'm sorry, but it turns out there is still a problem... none of the "letter" entry characters need to actually be letters to pass through your formula. Here is one of the several worst possibilities that your formula will return TRUE for... $+#%&1234* Rick > easily resolved > [quoted text clipped - 39 lines] >>>> regards >>>> krishna I'm beginning to think the only "foolproof" way to do this might be this formula... =AND(MID(A1,1,1)>="A",MID(A1,1,1)<="Z")+AND(MID(A1,2,1)>="A",MID(A1,2,1)<="Z")+AND(MID(A1,3,1)>="A",MID(A1,3,1)<="Z")+AND(MID(A1,4,1)>="A",MID(A1,4,1)<="Z")+AND(MID(A1,5,1)>="A",MID(A1,5,1)<="Z")+AND(MID(A1,6,1)>="0",MID(A1,6,1)<="9")+AND(MID(A1,7,1)>="0",MID(A1,7,1)<="9")+AND(MID(A1,8,1)>="0",MID(A1,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 > 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 [quoted text clipped - 48 lines] >>>>> regards >>>>> krishna 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 > I'm beginning to think the only "foolproof" way to do this might be this > formula... [quoted text clipped - 59 lines] > >>>>> regards > >>>>> krishna 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 > Hi rick, > [quoted text clipped - 77 lines] >> >>>>> regards >> >>>>> krishna =AND(SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CHAR(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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > I'm beginning to think the only "foolproof" way to do this might be this > formula... [quoted text clipped - 59 lines] >>>>>> regards >>>>>> krishna You will need to add something for the length... ABCDE1234FABCDEF evaluates to TRUE. Rick > =AND(SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CHAR(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))) > [quoted text clipped - 67 lines] >>>>>>> regards >>>>>>> krishna =AND(LEN(A1)=10,SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CHAR(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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > You will need to add something for the length... ABCDE1234FABCDEF > evaluates to TRUE. [quoted text clipped - 72 lines] >>>>>>>> regards >>>>>>>> krishna Still chokes: ?A?c*0000? *****1234* **a??0000x SignatureBiff Microsoft Excel MVP > =AND(LEN(A1)=10,SUM(--(NOT(ISNUMBER(MATCH(UPPER(MID(A1,ROW($1:$5),1)),CHAR(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))) [quoted text clipped - 78 lines] >>>>>>>>> regards >>>>>>>>> krishna 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,ROW($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 > Still chokes: > [quoted text clipped - 84 lines] >>>>>>>>>> regards >>>>>>>>>> krishna Accepts lower case letters: aBcDe9999F aaaaa0000a SignatureBiff Microsoft Excel MVP >I ***think*** this array-entered formula might work... > [quoted text clipped - 93 lines] >>>>>>>>>>> regards >>>>>>>>>>> krishna 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 > Accepts lower case letters: > [quoted text clipped - 98 lines] >>>>>>>>>>>> regards >>>>>>>>>>>> krishna >do you think the formula works shape-wise? Yeah, it works if case is not a consideration and rows will not be inserted. SignatureBiff Microsoft Excel MVP >I allowed for that on purpose since the OP didn't specifically say to >ignore them (his original requirement was only for "alphanumeric" [quoted text clipped - 112 lines] >>>>>>>>>>>>> regards >>>>>>>>>>>>> krishna The OP seems to be satisfied but.... The cell will accept: <space>~!<space>&1234) SignatureBiff Microsoft Excel MVP > easily resolved > [quoted text clipped - 39 lines] >>>> regards >>>> krishna In this example the AAAAI7504G type code is contained in C1: =AND(MID(C1,1,1)>="A",MID(C1,2,1)>="A",MID(C1,3,1)>="A",MID(C1,4,1)>="A",MID(C1,5,1)>="A",MID(C1,10,1)>="A",MID(C1,1,1)<="Z",MID(C1,2,1)<="Z",MID(C1,3,1)<="Z",MID(C1,4,1)<="Z",MID(C1,5,1)<="Z",MID(C1,10,1)<="Z",ISNUMBER(VALUE(MID(C1,6,4))),LEN(C1)=10) Regards, Stefi „Sai Krishna” ezt írta: > Hi, > [quoted text clipped - 18 lines] > regards > krishna The following formula will accept only capital letters of the alphabet in positions 1-5, only a number in positions 6-9 and only a capital letter in position 10. It is an array formula, hence commit with Shift +Ctrl+Enter =PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")), 1))>64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91))*ISNUMBER(-- MID(A2,6,3))*(CODE(RIGHT(A2,1))>64)*(CODE(RIGHT(A2,1))<91) HTH Kostis Vezerides On May 22, 7:57 am, Sai Krishna <SaiKris...@discussions.microsoft.com> wrote: > Hi, > [quoted text clipped - 18 lines] > regards > krishna Correction for the ISNUMBER part, MID(A2,6,4) is needed: =PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")), 1))>64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91))*ISNUMBER(-- MID(A2,6,4))*(CODE(RIGHT(A2,1))>64)*(CODE(RIGHT(A2,1))<91) Still an array formula. I tried to simplify the check for a number, although this will accept parts in positions 6-9 like: ABCDE-123G ABCDE1E10G If we want to guard against these possibilities then the more complex formula is warranted: =PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")), 1))>64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91))*PRODUCT(-- ISNUMBER(--MID(A2,ROW(INDIRECT("6:9")), 1)))*(CODE(RIGHT(A2,1))>64)*(CODE(RIGHT(A2,1))<91) HTH Kostis > 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 [quoted text clipped - 33 lines] > > regards > > krishna The ISNUMBER test appears to return 1 (TRUE) for floating point values, such as this... ABCDE1.34F Rick > Correction for the ISNUMBER part, MID(A2,6,4) is needed: > [quoted text clipped - 62 lines] >> > regards >> > krishna That will accept more than 10 characters. Slightly shorter but ugly as all get-out: =SUM(--(LEN(A1)=10),5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90"))),"")),--(COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4),--AND(CODE(RIGHT(A1))>64,CODE(RIGHT(A1))<91))=8 If you enter this as a worksheet function it has to be array entered. Meets these conditions: Length must be 10 characters The first 5 characters must be uppercase letters A-Z Characters 6:9 must be numbers 0-9 The last character must be uppercase letters A-Z SignatureBiff Microsoft Excel MVP > Correction for the ISNUMBER part, MID(A2,6,4) is needed: > [quoted text clipped - 62 lines] >> > regards >> > krishna Slightly shorter with 2 less function calls: =SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90"))),"")),COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4,LOOKUP(CODE(RIGHT(A1)),{65,90,91},{1,1,0}))=8 SignatureBiff Microsoft Excel MVP > That will accept more than 10 characters. > [quoted text clipped - 77 lines] >>> > regards >>> > krishna I'm not 100% sure, but I think you can remove those INDIRECT function calls... =SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(65:90)),"")),COUNT(-MID(A1,ROW(6:9),1))=4,LOOKUP(CODE(RIGHT(A1)),{65,90,91},{1,1,0}))=8 Giving it a quick test, it looks like this version of your formula (array-entered, of course) works. Rick > Slightly shorter with 2 less function calls: > [quoted text clipped - 81 lines] >>>> > regards >>>> > krishna Those INDIRECTs make it robust against row insertions. If rows will *never* be inserted then, yes, we can do without them. SignatureBiff Microsoft Excel MVP > I'm not 100% sure, but I think you can remove those INDIRECT function > calls... [quoted text clipped - 92 lines] >>>>> > regards >>>>> > krishna True, but they also make the formula volatile as well. Rick > Those INDIRECTs make it robust against row insertions. > [quoted text clipped - 99 lines] >>>>>> > regards >>>>>> > krishna Thanks for answer. Formula restricting the length to 10 characters would be great. regards krishna > The following formula will accept only capital letters of the alphabet > in positions 1-5, only a number in positions 6-9 and only a capital [quoted text clipped - 32 lines] > > regards > > krishna Just add a multiplication with (LEN(A2)=10) =(LEN(A2)=10)*PRODUCT((CODE(MID(A2,ROW(INDIRECT("1:5")), 1))>64)*(CODE(MID(A2,ROW(INDIRECT("1:5")),1))<91))*PRODUCT(-- ISNUMBER(--MID(A2,ROW(INDIRECT("6:9")), 1)))*(CODE(RIGHT(A2,1))>64)*(CODE(RIGHT(A2,1))<91) HTH Kostis On May 23, 10:27 am, Sai Krishna <SaiKris...@discussions.microsoft.com> wrote: > Thanks for answer. > [quoted text clipped - 38 lines] > > > regards > > > krishna |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.