Re: Cell validation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You will need to add something for the length... ABCDE1234FABCDEF evaluates to TRUE.

Rick


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message news:ummrfnPvIHA.5832@xxxxxxxxxxxxxxxxxxxxxxx
=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.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:uQTN6aCvIHA.4376@xxxxxxxxxxxxxxxxxxxxxxx
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


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:eMDbjQCvIHA.576@xxxxxxxxxxxxxxxxxxxxxxx
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" <bob.NGs@xxxxxxxxxxxxx> wrote in message news:eeqQQQ$uIHA.5244@xxxxxxxxxxxxxxxxxxxxxxx
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)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:%23su3e5%23uIHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
That formula is not foolproof; for example, consider these entries...

ABCDE12.3F
ABCDE+123F
ABCDE$123F
ABCDE(12)F

Rick


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message news:O52fyw%23uIHA.5448@xxxxxxxxxxxxxxxxxxxxxxx
=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" <SaiKrishna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:76C8786A-AAB0-4DAC-AD9E-65DF55972C42@xxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: Cell validation
    ... Microsoft Excel MVP ... characters). ... (there's no email, no snail mail, but somewhere should be gmail in my ... my addy) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Cell validation
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... although I do note some ALT+0### "type-able" keyboard characters will pass the test; however, it would be foolproof for the "normal" keyboard characters. ... First 5 should be alphabets, next 4 should be numbers and the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Cell validation
    ... (there's no email, no snail mail, but somewhere should be gmail in my ... it would be foolproof for the "normal" keyboard characters. ... been entered in place of an alphabet. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Cell validation
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... would be foolproof for the "normal" keyboard characters. ... been entered in place of an alphabet. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Cell validation
    ... Accepts lower case letters: ... (there's no email, no snail mail, but somewhere should be gmail in my ... it would be foolproof for the "normal" keyboard characters. ...
    (microsoft.public.excel.worksheet.functions)