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)<>" ")`  Reply With Quote

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

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

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  Reply With Quote

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)  Reply With Quote

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  Reply With Quote

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  Reply With Quote

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)  Reply With Quote

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)`  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

User Tag List

alphanumeric, data, error, excel, range 