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

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

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

3. ## 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:
`=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",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`

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

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

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

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

Code:
`=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",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,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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•