# Thread: Allow only certain alphanumeric data in a range Thanks:  4 Post #5278218 (1)Post #5278243 (1)Post #5278254 (1)Post #5278153 (1) Likes:  1 Post #5278263 (1)

1. ## 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.

2. ## Re: How to allow only certain alphanumeric data in a range

Originally Posted by patni
Hey Special k99,

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
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.

3. ## Re: Allow only certain alphanumeric data in a range

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```

4. ## 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.

THANK YOU

5. ## Re: How to allow only certain alphanumeric data in a range

Hey Special K99,

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:
`=COUNTIF(\$A:\$A,A11)=1`
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

6. ## 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

7. ## 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)

8. ## Re: How to allow only certain alphanumeric data in a range

Maybe this:-
NB:- This code should also prevent duplicates
Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range, n As Long, Ray As Variant, Dic As Object, Num As Long
Dim WS      As Worksheet
Dim shp     As Shape
Dim i As Long
Dim lastRow As Long
Dim tRng    As Range
Dim shpName As String
Dim shpPath As String
Dim filepath As String
If Target.Column = 3 Then
Application.EnableEvents = False
Cells(Target.Row, 17).Value = Date + Time
Application.EnableEvents = True

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
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
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)
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
End If
End With

ElseIf Target.Column = 1 Then

Set Rng = Range("A1:A100")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Ray = Array("AK", "OR")
For n = 0 To 1
For Num = 1 To 100
Dic(Ray(n) & Num) = Empty
Next Num
Next n
End If
Application.EnableEvents = False
If Target.Count = 1 Then
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not Dn.Value = vbNullString Then
If Not .exists(Dn.Value) Then
Else
Dn.Value = ""
MsgBox "Duplicate Entry"
End If
End If
Next Dn
End With
If Not Target.Value = vbNullString Then
If Not Dic.exists(Target.Value) Then
Target = ""
MsgBox "Invalid Entry"
End If
End If
Application.EnableEvents = True
End If

End Sub```
Regards Mick

9. ## 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

10. ## 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