# Allow only certain alphanumeric data in a range

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

Hey Rick Rothstein,
I just checked that.

Okay this is something intresting. Is there any ways to prevent this ?
Just very curious

Last edited:

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

Hey Special K 99,

Is it possible to add 2 more criteria in the code ? (Sorry for the trouble)
I mean we have "AK" and "OR" in the code below.

Is it possible to add "LE" and "LK" as well?

So total we have 4 criteria "AK" "OR" "LE" "LK"

Code:
``[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$8:A\$1000,A8)=1[/COLOR]``

Last edited:

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

I tried this but something is wrong I guess. could you check please.
THANK YOU SO MUCH

Code:
``[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$8:A\$1000,A8)=1``

#### Rick Rothstein

##### MrExcel MVP
Re: How to allow only certain alphanumeric data in a range

Hey Rick Rothstein,
I just checked that.

Okay this is something intresting. Is there any ways to prevent this ?
Just very curious
The only way to have fool-proof data validation is by using properly designed Change event coding.

#### Special-K99

##### Well-known Member
Re: How to allow only certain alphanumeric data in a range

I tried this but something is wrong I guess. could you check please.
THANK YOU SO MUCH

Code:
``[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1[color=red][b],[/b][/color]RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$8:A\$1000,A8)=1``
You've deleted a comma after the second RIGHT, see highlighted

#### Special-K99

##### Well-known Member
Re: How to allow only certain alphanumeric data in a range

And you've deleted the closing brackets and changed the COUNTIF formula so it doesnt work
This is what it should be

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR")))),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$1:A\$1000,A8)=1)

If you're gonna change cell references in this formula then only change the references to cell A8 - NO OTHER CELL REFERENCES or you're undoing the work I've done for you.

Last edited:

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

Hey Special K99 ,
Sure.
Thank you so much for the help.
I checked my mistake and hopefully i learn from it.

The code works perfectly now.
Thank you for the help

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
I need help regarding this thread.
In the previous formulae we had allowed "LK" , "AK", "AE", OR" codes from 1 to 100

I want to add 3 more to this formulae. I want to add "NL" , "BA" , and "CH" (same as previous from 1 to 100)

Could you help me please. I am unable to crack it.
Thank you

And you've deleted the closing brackets and changed the COUNTIF formula so it doesnt work
This is what it should be

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR")))),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$1:A\$1000,A8)=1)

If you're gonna change cell references in this formula then only change the references to cell A8 - NO OTHER CELL REFERENCES or you're undoing the work I've done for you.

#### Special-K99

##### Well-known Member
Re: How to allow only certain alphanumeric data in a range

Try (untested)

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="AK",LEFT(A8,2)="OR",LEFT(A8,2)="NL",LEFT(A8,2)="BA",LEFT(A8,2)="CH"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$1:A\$1000,A8)=1)

Last edited:

#### patni

##### Board Regular
Re: How to allow only certain alphanumeric data in a range

Hey Special K99,

The above code is not working. I am not able to input any data as of now.
Whatever I type, excel data valididation stops me from typing.

Try (untested)

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="AK",LEFT(A8,2)="OR",LEFT(A8,2)="NL",LEFT(A8,2)="BA",LEFT(A8,2)="CH"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A\$1:A\$1000,A8)=1)