Allow only certain alphanumeric data in a range

P

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:
Sort by date Sort by votes
Re: How to allow only certain alphanumeric data in a range

What range are you working with? the formula you posted checked A11 so that is what I used. The formula should use the first cell in the range. So if it is for the whole column then use A1 if it starts in A11 then use A11 in the formula.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Re: How to allow only certain alphanumeric data in a range

patni said:
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
Click to expand...

You need to put an = before the formula
I've used A1, adjust the cell in the formula to where you are storing the data.
 
Last edited:
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "" Then Exit Sub
    If IsNumeric(Right(Target.Value, Len(Target.Value) - 2)) And _
        (Left(Target.Value) = "AK" Or Left(Target.Value) = "OR") Then
    Else
        MsgBox "Wrong Code Entered"
        
        'to set the cell back to blank
        Target.Value = ""
    End If
 
End Sub
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Hey Mick,

Also if I enter any random data suppose "DR56" , then it is accepting it.
It should not be accepting it but.

Could you please check once.

THANK YOU
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Hey Special K99,
i made it to this.

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

However 2 problem I am facing,

1st is that i need to mearge your code with the
Code: 
[COLOR=#333333]=COUNTIF($A:$A,A11)=1[/COLOR]
This is to prevent duplicate entries
2nd is that suppose I enter OR (SPACE) 50 , then it is accepting.
It would be better if it could allow only OR50 . It should show error even if it contains a space
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Hey Scott,

I will just do that and update you on it.
Thank you for the guidance, It means a lot.
Thank you
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Try

=AND(OR(A99="OR 50",AND(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)
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Maybe this:-
NB:- This code should also prevent duplicates
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, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] WS      [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] shp     [COLOR="Navy"]As[/COLOR] Shape
[COLOR="Navy"]Dim[/COLOR] addr    [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] lastRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] tRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] shpName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] shpPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] filepath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Column = 3 [COLOR="Navy"]Then[/COLOR]
    Application.EnableEvents = False
    Cells(Target.Row, 17).Value = Date + Time
    Application.EnableEvents = True




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


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


[COLOR="Navy"]ElseIf[/COLOR] Target.Column = 1 [COLOR="Navy"]Then[/COLOR]

[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A100")
[COLOR="Navy"]If[/COLOR] Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/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] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
              .CompareMode = vbTextCompare
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
               [COLOR="Navy"]If[/COLOR] Not Dn.Value = vbNullString [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                   .Add (Dn.Value), ""
                [COLOR="Navy"]Else[/COLOR]
                    Dn.Value = ""
                    MsgBox "Duplicate Entry"
                [COLOR="Navy"]End[/COLOR] If
             [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn
     [COLOR="Navy"]End[/COLOR] With
     [COLOR="Navy"]If[/COLOR] Not Target.Value = vbNullString [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
            Target = ""
            MsgBox "Invalid Entry"
        [COLOR="Navy"]End[/COLOR] If
     [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

Hey Special K99,
Will it do for other alpha numeric datas also?

Example : If i write AK (SPACE) 56 or OR (SPACE) 91 etc, will it prevent them also.
I mean I need it to be without spaces. I dont want it to allow spaces in between for all data
 
Upvote 0
Re: How to allow only certain alphanumeric data in a range

Hey Mike,
I guess there os something wrong because it is accepting any data as of now.
I entered "KL1" and it allowed
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Locked
  • Solved
Data Validation for Alphanumeric and Hyphens (dashes) for a column
Replies
7
Views
1K
Peter_SSs
Peter_SSs
G
  • Solved
VBA to allow grouping +- after reopening of the sheet
Replies
7
Views
749
Greenbehindthecells
G
R
  • Solved
VBA code to allow only numeric values in a cell
Replies
6
Views
1K
Legacy 143009
L
M
  • Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
166
mecerrato
M
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
276
Gajendran Yadhav
G

Forum statistics

Threads
1,214,787
Messages
6,121,558
Members
449,038
Latest member
Guest1337

Share this page

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