Cell validation

S

Sai Krishna

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
 
B

Bob Phillips

=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)
 
R

Rick Rothstein \(MVP - VB\)

That formula is not foolproof; for example, consider these entries...

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

Rick
 
B

Bob Phillips

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)
 
S

Stefi

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:
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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


message news:[email protected]...
 
V

vezerid

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
 
V

vezerid

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
 
R

Rick Rothstein \(MVP - VB\)

The ISNUMBER test appears to return 1 (TRUE) for floating point values, such
as this...

ABCDE1.34F

Rick
 
T

T. Valko

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
 
S

Sai 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
 
S

Sai Krishna

Thanks for answer.

Formula restricting the length to 10 characters would be great.
regards
krishna
 
R

Rick Rothstein \(MVP - VB\)

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
 
V

vezerid

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
 
B

Bob Phillips

=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)



message news:[email protected]...
 
R

Rick Rothstein \(MVP - VB\)

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

Rick
 
B

Bob Phillips

=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.

--
---
HTH

Bob


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



message news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Found few bugs in my daily routine of work in excel for MAC 0
Cumulative formula for word table 0
Possible to Combine reports to's to a single box? 0
Excel does not accept text format! 0
Outlook meeting location auto prompts with a suggestion list - need to disable 0
Validation Rules for a Control on a Form (?) 4
Converting URLs in comments to Markdown-formatted links 2
Excel 2013: How to ensure a cell is not left blank by users? (Not using VBA) 0

Top