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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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

D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
424
Alex Blakenburg
A
H
  • Question Question
VBA Pivot Table Dynamic Range & Only One Selection per Row
Replies
3
Views
296
DanteAmor
DanteAmor
A
  • Question Question
How do I lock a worksheet and stop data entry after a certain date and time?
Replies
1
Views
565
Jeffrey Mahoney
Jeffrey Mahoney
JenniferMurphy
  • Question Question
Is there a way I can allow a group of users to enter their own data?
Replies
7
Views
482
JenniferMurphy
JenniferMurphy
V
  • Locked
  • Solved
Data Validation for Alphanumeric and Hyphens (dashes) for a column
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,928
Messages
6,181,806
Members
453,067
Latest member
mdiz777

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