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:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Yes, it has your original prevent duplicate formula.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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