Allow only certain alphanumeric data in a range

[patni]

Hey guys,
I need a small help.

In my sheet "PRODUCT" range "A1:A150" I want it to only only certain alphanumeric data.
I want it to allow only "AK1, AK2, AK3, AK4, AK5 ....... AK100" and "OR1, OR2, OR3, OR4, OR5 ..... OR100"

If a user enters anything other than this, then I dont want excel to allow it. Probably give a error message saying "Wrong Code Entered"

Also , I already have this in my data valididation

=COUNTIF($A:$A,A11)=1

. This prevents duplicate entries

Can anyone please help me.

THANK YOU

 

[Scott T]

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

Adjusted to prevent space before number

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

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

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

 

[patni]

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

Hey Scott,
updated the code to this, (only a1 changed to a8)

=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

 

[Special-K99]

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]

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]

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]

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)

 

[patni]

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

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

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]

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.