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!

 

[Peter_SSs]

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

Best regards,

You are welcome. I have noticed a slight issue with the code I posted in post #9. If a cell entry is made with a leading apostrophe and any other disallowed character(s) in the cell then the message box at the end reports that cell twice. If that is an issue for you, try replacing the central part of the code with this:

For Each cel In Changed
  s = cel.Value
  If cel.PrefixCharacter = "'" Then
    sErrors = sErrors & vbLf & cel.Address(0, 0) & vbTab & "'" & s
    cel.ClearContents
  ElseIf .Test(s) Then
    sErrors = sErrors & vbLf & cel.Address(0, 0) & vbTab & s
    cel.ClearContents
  End If
Next cel

 

[rkarthik]

Simple, Elegant and Awesome! Bow to thee Excel Master