Allow only certain alphanumeric data in a range

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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)<>" ")
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

patni

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

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

patni

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

Special-K99

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

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)
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

patni

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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
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)
 
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,
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:
[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]
 

patni

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,952
Office Version
2010
Platform
Windows
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.
Just so you know, Data Validation will not prevent someone from copying bad data from a different location and pasting into the "protected" cell.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,931
Messages
5,471,585
Members
406,770
Latest member
livetolearn4life

This Week's Hot Topics

Top