Restricting Cell Entries to Only Contain Alphanumeric and Other Specific Characters

[shawthingz]

Hi Folks,

I thought I'd managed to build a "bullet-proof" Excel solution for a specific Business process, but someone's just managed to break it

Here's a description of the problem I'm now facing:

I need to restrict the set of characters that a user can enter into a set of cells (which I believe I can do using the Custom Validation feature). The field length is variable.


The reason for this is that the final part of the process involves an upload to a DB so there are a limited set of characters I want to support, which are:

[a-z],[A-Z],[0-9],"-","_"

(Note that this may change in the future, so any solution needs to be easy to update)


If it helps, I've already found the following suggestion, but unfortunately it doesn't appear to work (& my feeble brain can't work out why)

>> Assume the input cell is A1, try this

>> =IF(A1="","",IF( ISERROR( SUMPRODUCT( SEARCH(MID( A1,ROW( INDIRECT( "1:"&LEN(A1))), 1), "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-_" ))),"ERROR",A1))

Any cell validation formula I plug-in would need to work with Excel 2003, 2007 & 2010


Any guidance on how to solve this problem would be much appreciated!

 

[Andrew Poulsom]

In Data Validation try:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-_")))

 

[shawthingz]

Many Thanks,

This appears to work perfectly (& is simpler than the VBA I was attempting to write in parallel to solve this problem)!

 

[Peter_SSs]

If this did work as required, then since SEARCH (not case sensitive) has been used, you could shorten that by eliminating either the upper or lower case letters from the string.

However, because SEARCH has been used, that this will allow the wildcard characters "?" and "*" to be included in your cell. Therefore I suggest changing the SEARCH to FIND (case sensitive) and leaving both upper & lower case letters in the string.

 

[Peter_SSs]

BTW, both Data Validation and vba approaches have advantages/disadvantages.

Data Validation will work without the user enabling macros, but is defeated if the user pastes data into the relevant cell(s).
vba overcomes the pasting problem but is defeated if the user does not enable macros. The code as written below would also fail if rows/columns were added/removed above/left of the myTarget range.

If you wanted to consider a vba approach, here is one. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range, cel <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sErrors <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myTarget <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C1:C100"    <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Range(myTarget), Target)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .IgnoreCase = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "[^0-9a-z-_]"<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> Changed<br>                s = cel.Value<br>                <SPAN style="color:#00007F">If</SPAN> .Test(s) <SPAN style="color:#00007F">Then</SPAN><br>                    sErrors = sErrors & vbLf & cel.Address(0, 0) _<br>                        & vbTab & s<br>                    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                    cel.ClearContents<br>                    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> cel<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Len(sErrors) <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "These cells had invalid entries and have been cleared:" _<br>                & sErrors<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Rachasak]

Hi Peter_SSs,
I put the code in Sheet Code. The Code is too difficult for me.
I've changed .Pattern = "[^0-9a-z-_]" to .Pattern = "[^0-9a-z]"
(don't want user to input underscore) It works very well.

I'd like to ask a question.
Cell can accept a single quote. How can I change the code to reject a single quote?

 

[Peter_SSs]

I'd like to ask a question.
Cell can accept a single quote. How can I change the code to reject a single quote?

Welcome to the MrExcel board!

Do you mean ..
a) You want to reject the entry if it is a single quote and nothing else? Example '
b) You want to reject the entry if it is prefixed by a single quote (That is, signifying a text entry but not part of the actual cell contents)? Example 'abc123
c) You want to reject the entry if there is a single quote character anywhere in the cell? Example abc'123
d) Some combination of the above or something else altogether?

What version of Excel are you using?

 

[Rachasak]

I use Excel 2016. I've meant a: ' and b: 'abc123.
Your code works well, rejecting the entry like c: abc'123.
Thanks a lot
Best regards,

 

[Peter_SSs]

I use Excel 2016. I've meant a: ' and b: 'abc123.

In that case, see how this goes:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, cel As Range
  Dim s As String, sErrors As String

  Const myTarget As String = "C1:C100"    '<- Change to suit

  Set Changed = Intersect(Range(myTarget), Target)
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    With CreateObject("VBScript.RegExp")
      .Global = True
      .IgnoreCase = True
      .Pattern = "[^0-9a-z]"
      For Each cel In Changed
        s = cel.Value
        If cel.PrefixCharacter = "'" Then
          sErrors = sErrors & vbLf & cel.Address(0, 0) & vbTab & "'" & s
          cel.ClearContents
        End If
        If .Test(s) Then
          sErrors = sErrors & vbLf & cel.Address(0, 0) & vbTab & s
          cel.ClearContents
        End If
      Next cel
    End With
    If Len(sErrors) Then
      MsgBox "These cells had invalid entries and have been cleared:" & sErrors
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[Rachasak]

I've learnt a lot from your codes.
Thank you very much.

Best regards,