Data Validation - Restrict cell with multiple conditions

Dear folks,

Much appreciate your help with below case:

Restrict cell A1 with 2 conditions:
- A1 must be alphanumeric
- Length must be < 19

I try OR(AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122),AND(CODE(A1)>=48,CODE(A1)<=57)) with the first condition but it seems like it only checks the first character of the cell.

Thank you very much.

First, you can use HOME/CONDITIONAL FORMATTING....cell containing value between 0 (zero) and Z
Then, DATA/DATA VALIDATION - Text Length between 0 (zero) and 18

the formula
=AND(ISNUMBER(A1),LEN(A1)<19)
will check if the cell is numeric only, not applicable for this case when Alphanumeric is allowed (means whole numbers (0, 1, ...) & alphas (a, b, c, A, B, C, ...).

Originally Posted by x65140

First, you can use HOME/CONDITIONAL FORMATTING....cell containing value between 0 (zero) and Z
Then, DATA/DATA VALIDATION - Text Length between 0 (zero) and 18

Use Data validation as x65140 suggested

Allow: Text length
Data: Less than
Maximum: 19

What about trying this.......Data Validation

=OR((AND(A1>=0,A1<=999999999999999999,LEN(A1<19))),AND(A1>="a",A1<="ZZZZZZZZZZZZZZZZZZ",LEN(A1)<19))

It is AlphaNumeric....and will NOT let the FIRST byte be a special character...but still allows other bytes to be special....
(will NOT allow '@ ' but will allow 'asw@ '
does that help????

Originally Posted by wenqq3

Please Login or Register  to view this content.

How about this, it will not accept any number.

What I need is both alphabets & numbers should be allowed to key in.
Thanks for trying.

Originally Posted by x65140

What about trying this.......Data Validation

=OR((AND(A1>=0,A1<=999999999999999999,LEN(A1<19))),AND(A1>="a",A1<="ZZZZZZZZZZZZZZZZZZ",LEN(A1)<19))

It is AlphaNumeric....and will NOT let the FIRST byte be a special character...but still allows other bytes to be special....
(will NOT allow '@ ' but will allow 'asw@ '

does that help????

It should prevent user from keying in other characters but alphabets & numbers.
Thanks for trying.

Originally Posted by Rambo4711

Use Data validation as x65140 suggested

Allow: Text length
Data: Less than
Maximum: 19

As I said, the conditional formatting won't prevent characters other than Alphabet & numbers.

Originally Posted by Tony Valko

If you want to allow only the letters A - Z, a - z and the digits 0-9...

Create this defined named expression.

Goto the Formulas tab>Define Name
Name: String
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
OK

Then, use this formula for the data validation:

=AND(FIND(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),String),LEN(A1)<19)

When setting up the validation uncheck: Ignore blank

If you test that formula in a worksheet cell it must be array entered.

array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks a lot, I tried and it worked with similar formula:

Please Login or Register  to view this content.