Allow only certain alphanumeric data in a range

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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:
[COLOR=#333333]=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[/COLOR]
 
Last edited:

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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:
[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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
The only way to have fool-proof data validation is by using properly designed Change event coding.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
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:
[COLOR=#333333]=AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",[/COLOR]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[color=red][b],[/b][/color]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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
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.
 
Last edited:

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
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
 

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
I need help regarding this thread.
In the previous formulae we had allowed "LK" , "AK", "AE", OR" codes from 1 to 100

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

Could you help me please. I am unable to crack it.
Thank you




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.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
Re: How to allow only certain alphanumeric data in a range

Try (untested)

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

patni

Board Regular
Joined
Jul 22, 2018
Messages
58
Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
thank you for the reply.

The above code is not working. I am not able to input any data as of now.
Whatever I type, excel data valididation stops me from typing.


Try (untested)

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

Watch MrExcel Video

Forum statistics

Threads
1,095,971
Messages
5,447,598
Members
405,459
Latest member
newbie111

This Week's Hot Topics

Top