Restricting data entry to A-Z a-z 0-9

[Illya Teideman]

Is there an easy way of restricting the data that can be entered into a cell
so that only characters that are in the range A to Z a to z 0 to 9 are valid.
i.e !"£$%^&*()-_ etc etc are not allowed? Might also need space to be an
allowed character, not sure yet.

 

[Teethless mama]

Enter this formula in the Data Validation

=OR(AND(CODE(A1)>=CODE("A"),CODE(A1)<=CODE("Z")),AND(CODE(A1)>=CODE("a"),CODE(A1)<=CODE("z")),AND(A1>=0,A1<=9))

 

[Ron Rosenfeld]

Is there an easy way of restricting the data that can be entered into a cell
so that only characters that are in the range A to Z a to z 0 to 9 are valid.
i.e !"£$%^&*()-_ etc etc are not allowed? Might also need space to be an
allowed character, not sure yet.

There may be easier methods, but I'm in a hurry.

One method is to write a short UDF that will return FALSE or TRUE depending on
the nature of the entry.

Insert that formula into some cell referring to your data entry cell.

Then use Data Validation on your data entry cell.

e.g.

Enter your formula in some cell:

=chkentry(Data_Entry_Cell,"[A-Za-z0-9]")

If you need to add a <space>, or any other character, you can add it between
the brackets.

Enter this UDF in a regular module:

===================================
Option Explicit
Function ChkEntry(str As String, Ptrn As String) As Boolean
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Pattern = Left(Ptrn, 1) & "^" & Mid(Ptrn, 2, 255)
ChkEntry = Not re.test(str)
End Function
=====================================

It will return FALSE if there are any characters in the Data_Entry_Cell other
than those between the brackets.

Then use Data/Validation/Custom on the Data Entry cell, referring to the
"ChkEntry" cell.

I may be wrong, but I don't believe you can refer directly to a UDF from the
Data Validation dialog box, rather you have to refer to another cell that
contains the results of the UDF.
--ron

 

[Ron Rosenfeld]

Enter this formula in the Data Validation

=OR(AND(CODE(A1)>=CODE("A"),CODE(A1)<=CODE("Z")),AND(CODE(A1)>=CODE("a"),CODE(A1)<=CODE("z")),AND(A1>=0,A1<=9))

I interpreted the OP request differently.

It may or may not be what the OP wants, but I want to point out that your code
will only work for the first character in the entry.

It will return TRUE for a#$% for example


--ron

 

[Illya Teideman]

Thanks for the replies. I'm looking into both of them. I will try it out and
let you know how I get on. Any more ideas more than welcome.

 

[T. Valko]

This will allow only A-Z, a-z, 0-9:

=SUM(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))+SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("48:57"))),"")))=LEN(A1)

 

[Illya Teideman]

The data validation solution works well as long as I am manually entering the
data. Any ideas how I can maintain the validation rules when copying and
pasting from another external sheet?

 

[T. Valko]

That's the weakness of data validation. It won't work when doing those
operations. I don't know of any way to correct that. Perhaps an event macro
that would pop a message box but I don't know how to go about that.

 

[Ron Rosenfeld]

The data validation solution works well as long as I am manually entering the
data. Any ideas how I can maintain the validation rules when copying and
pasting from another external sheet?

You can do that with an event-triggered macro.

Right-click on the worksheet tab, and select View Code

Paste the code below into the window that opens.

Read the comments in the code and make the appropriate changes.

=============================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim re As Object
Dim c As Range
Const sPattern As String = "[^A-Za-z0-9]"

Set c = [a1] 'set this to the cell you wish to validate
'if there is more than one cell, this can
'be set to a range, but you will then need
'a loop below to check each cell in the range

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern


If re.test(c.Text) = True Then
MsgBox ("Illegal Characters")
c.ClearContents
End If
End Sub
===========================================
--ron

 

[Illya Teideman]

Thanks for the help I used a little bit of everything in the end but it was
all useful info.

The data validation solution works well as long as I am manually entering the
data. Any ideas how I can maintain the validation rules when copying and
pasting from another external sheet?

You can do that with an event-triggered macro.

Right-click on the worksheet tab, and select View Code

Paste the code below into the window that opens.

Read the comments in the code and make the appropriate changes.

=============================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim re As Object
Dim c As Range
Const sPattern As String = "[^A-Za-z0-9]"

Set c = [a1] 'set this to the cell you wish to validate
'if there is more than one cell, this can
'be set to a range, but you will then need
'a loop below to check each cell in the range

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern


If re.test(c.Text) = True Then
MsgBox ("Illegal Characters")
c.ClearContents
End If
End Sub
===========================================
--ron

 

[Ron Rosenfeld]

Thanks for the help I used a little bit of everything in the end but it was
all useful info.

You're welcome. Glad to help. Thanks for the feedback.
--ron