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

 

[patni]

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

Hey Special K99,

I tried this. But this is not working. Could you pleasee check once

[TABLE="width: 264"]
<tbody>[TR]
[TD]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="BA",OR(LEFT(A8,2)="CH",OR(LEFT(A8,2)="NK",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)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

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

OK, I've gone back to your post #28 where you've said it's all working fine.

Try

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

 

[patni]

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

Hey Special K99,'

I want to add 3 more to this formulae. I want to add "NL" , "BA" , and "CH" (same as previous from 1 to 100)

I want to add these as well. I above code provided code does not allow me to write NL, BA, and CH

Could you check once.
Thank you so much

 

[Special-K99]

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

Just look at the codes you wanted at the beginning of this problem AK and OR, see what it looks like in the formula - OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR")
Look at the other codes you've asked for, LE and LK, and see how I've added them to the formula - OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR",LEFT(A8,2)="LE",LEFT(A8,2)="LK")

Just add the new codes you want in the same way LEFT(A8,2)=code, separated by a comma for each code you want.

Now you can add as many codes as you want.

This would have been solved much quicker if you'd have planned everything out in the first place and decided what you wanted at the beginning.

 

[patni]

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

Hey Special K99,

Thank you for the help.
This is what I had done

=AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",OR(LEFT(A8,2)="BA",OR(LEFT(A8,2)="CH",OR(LEFT(A8,2)="NK",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)

I observed and found out the error ,

Now with all your guidance , mt code is working.

Thank you so much for your constant help and support