# Thread: Allow only certain alphanumeric data in a range Thanks:  4 Post #5278243 (1)Post #5278254 (1)Post #5278153 (1)Post #5278218 (1) Likes:  1 Post #5278263 (1)

1. ## 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)<>" ")`

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

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

3. ## 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

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

Originally Posted by patni
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)

5. ## 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

6. ## 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

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

Originally Posted by patni
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)

8. ## 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:
`=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)`

9. ## 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

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

Originally Posted by patni
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.