# Allow only certain alphanumeric data in a range

#### Scott T

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

Adjusted to prevent space before number
Code:
``=AND(COUNTIF(\$A:\$A,A1)=1,OR(LEFT(A1,2)="AK",LEFT(A1,2)="OR"),--RIGHT(A1,LEN(A1)-2)>=1,ISNUMBER(--RIGHT(A1,LEN(A1)-2)),--RIGHT(A1,LEN(A1)-2)<=100,MID(A1,3,1)<>" ")``

• patni

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### patni

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

Hey Special K99,
I think we are close to the result

#### patni

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

Hey Scott,
updated the code to this, (only a1 changed to a8)
Code:
``=AND(COUNTIF(\$A:\$A,A8)=1,OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR"),--RIGHT(A8,LEN(A8)-2)>=1,ISNUMBER(--RIGHT(A8,LEN(A8)-2)),--RIGHT(A8,LEN(A8)-2)<=100,MID(A8,3,1)<>" ")``
However right now it is accepting any data in it

Last edited:

#### Special-K99

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

Hey Special K99,
I think we are close to the result
Badly worded description of what you wanted, sounded like you wanted "OR 50"

Try this, avoids spaces, must begin AK or OR, end with a number from 1-100, and not be duplicated from A1:A1000

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

#### MickG

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

Are you sure the code is running and/Or your "Application.EnableEvents" are set to true

• patni

#### patni

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

Hey SPecial K99,

I used the above code,

However strangely it is accepting any data in it.

Example : i wrote "HT1" and it accepted

#### Special-K99

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

Hey SPecial K99,

I used the above code,

However strangely it is accepting any data in it.

Example : i wrote "HT1" and it accepted
My fauilt, wrong way round, need a NOT on the space check

Try

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

Last edited:
• patni

#### patni

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

Hey Special K99,
I changed the code to this (changed a99 to a8)
It works perfectly now. It even prevents duplicate entries.

Thank you so much Special K 99

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]``

#### patni

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

THANK YOU EVERYONE

thank you for all the help, knowledge, time and patience.

Thank you so much

#### Rick Rothstein

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

Hey Special K99,
I changed the code to this (changed a99 to a8)
It works perfectly now. It even prevents duplicate entries.
Just so you know, Data Validation will not prevent someone from copying bad data from a different location and pasting into the "protected" cell.

• Special-K99