Allow only certain alphanumeric data in a range

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
Code:
=COUNTIF($A:$A,A11)=1
. This prevents duplicate entries

Can anyone please help me.

THANK YOU
 
Last edited:
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

Code:
[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]
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
Code:
=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
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,575
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top