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:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Re: How to allow only certain alphanumeric data in a range

If your happy to use a VbA "Change Event , try this:-
To load code:-
Right click sheet tab >> Select View Code>> Vb window appears>> Paste code into vbWindow>> close Vb Window.
To run code enter data in any cell "A1 A100".
Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A100")
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dic [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
                Dic.CompareMode = vbTextCompare
                Ray = Array("AK", "OR")
                [COLOR="Navy"]For[/COLOR] n = 0 To 1
                    [COLOR="Navy"]For[/COLOR] Num = 1 To 100
                        Dic(Ray(n) & Num) = Empty
                    [COLOR="Navy"]Next[/COLOR] Num
                [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]End[/COLOR] If
     Application.EnableEvents = False
     [COLOR="Navy"]If[/COLOR] Not Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
        MsgBox "Entry Not Valid"
        Target = ""
     [COLOR="Navy"]End[/COLOR] If
     Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,610
Office Version
365, 2016
Platform
Windows
Re: How to allow only certain alphanumeric data in a range

In Data validation try
Code:
=AND(COUNTIF($A:$A,A11)=1,OR(LEFT(A11,2)="AK",LEFT(A11,2)="OR"),--RIGHT(A11,LEN(A11)-2)>=1,ISNUMBER(--RIGHT(A11,LEN(A11)-2)),--RIGHT(A11,LEN(A11)-2)<=100)
 

patni

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

Hey Mick,

Sure . Thank you so much. Thank you.
I will just try loading the code and let you know the result.

Thank you for your reply
 

patni

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

Hey Scott,
Thank you so much for your reply
Will this also prevent duplicate entries in the range ?
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,610
Office Version
365, 2016
Platform
Windows
Re: How to allow only certain alphanumeric data in a range

Yes, it has your original prevent duplicate formula.
 

Special-K99

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

Try Data Validation

Custom Formula

AND(OR(LEFT(A1,2)="AK",LEFT(A1,2)="OR"),RIGHT(A1,LEN(A1)-2)+0>=1,RIGHT(A1,LEN(A1)-2)+0<=100)
 

patni

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

Hey Mick,

I already have this worksheet change code in my workbook

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
    Application.EnableEvents = False
    Cells(Target.Row, 17).Value = Date + Time
    Application.EnableEvents = True
End If
Dim WS      As Worksheet
Dim shp     As Shape
Dim addr    As Variant
Dim i As Long
Dim lastRow As Long
Dim tRng    As Range
Dim shpName As String
Dim shpPath As String


Set WS = ActiveSheet
shpPath = filepath
If Right(shpPath, 1) <> Application.PathSeparator Then shpPath = shpPath & Application.PathSeparator
If Dir(shpPath) = "" Then MsgBox shpPath & " is invalid!", vbCritical, "INVALID PATH": Exit Sub


lastRow = WorksheetFunction.Max(8, WS.Cells(Rows.Count, "A").End(xlUp).Row + 2)
If Not Intersect(Target, Range("A8:A" & lastRow)) Is Nothing Then
    Set tRng = Cells(Target.Row, "T")
    On Error Resume Next
    If Len(Trim(Target.Value)) = 0 Then
        On Error Resume Next
        Me.Shapes("PictureAt" & tRng.Address).Delete
        Err.Clear
        On Error GoTo 0
    Else
        Err.Clear
        On Error GoTo 0
        shpName = Target.Value
        If Len(Trim(shpName)) > 0 Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With
            With tRng
                .RowHeight = 56
                .ClearContents
                On Error Resume Next
                Me.Shapes("PictureAt" & .Address).Delete
                On Error GoTo 0
            End With
            If Dir(shpPath & Target & ".jpg") <> "" Then   '*  verify that the file exists
                With tRng
                    Set shp = Me.Shapes.AddPicture(shpPath & shpName & ".jpg", msoFalse, msoCTrue, .Left, .Top, .Width, .Height)
                    shp.Name = "PictureAt" & .Address
                End With
            Else
                tRng.Value = "NO IMAGE" & Chr(10) & "FOUND"
            End If
        End If
    End If
End If
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    If (Intersect(Target, Range("A1:G2")) Is Nothing) Then Exit Sub
    If Range("A7:Z500000").CurrentRegion.Rows.Count > 1 Then
        Range("A7:Z500000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2")
    End If
End With
End Sub
Could you help me mearge the above code with yours ?
Thank you
 

patni

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

Hey Scott ,
It isnt working in data validition.
If i write any random data suppose "kkkk" , then it is accepting it

Ideally it should not be accepting it. It should be accepting only certain alphanumeric datas

Could you please check once.

THANK YOU SO MUCh
 
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 your reply.
However it is preventing me from entering any data in it.
If i try to write "OR12" , then it does not allow me. I am not able to entry any data
 

Watch MrExcel Video

Forum statistics

Threads
1,096,146
Messages
5,448,601
Members
405,522
Latest member
NomanAziz

This Week's Hot Topics

Top