Identify certain ASCII characters in a text.

Hello, I have to clean up a database that might contain characters with text not compliant to an old application.

Permitted text is:
A-Z (capitals and small letters),
0-9,
. (dot),
, (comma),
- (dash),
(space)

OBJECTIVE: If a cell contains characters other than those, excel should return an information stating this (no need to specify which position the error is at). A simple message is enough.

I would need this as a formula in some way, or a macro that will automatically run without manual input such as clicking on a button or so...

Is this feasible and if, could you let me know how I can accomplish this?
The field length is usually 128 characters.

I thank you all and know, that if nobody can answer me here, it probably can't be done (which I doubt ;-)

Thanks!!!

Titus.

Here'a a macro that will work for you:

Sub Auto_Open()
' Declare variables/data types...
Dim charValue, Msg, Title As String
Dim cellTotal, cellCounter, stringLength, errorFlag, y As Integer
Dim c, Style, Response, oldStatusBar As Variant
    
    ' Show status bar to use for updating progress to user...
    If Application.DisplayStatusBar = False Then
        Application.DisplayStatusBar = True
    End If
    ' Get the total number of cells to check for erroneous characters...
    cellTotal = ActiveSheet.UsedRange.Cells.Count
    ' Initialize progress counter
    cellCounter = 0
    ' Start routine of checking every cell used in spreadsheet...
    For Each c In ActiveSheet.UsedRange
        ' Increment progress counter by 1...
        cellCounter = cellCounter + 1
        ' Display progress to user...
        Application.StatusBar = "Checking cell " & cellCounter & " of " & cellTotal & ", please wait..."
        ' Get the total number of characters in the cell to use as upper limit of verification loop...
        stringLength = Len(c.Value)
        ' Start the verification loop if the cell contains any data...
        If stringLength > 0 Then
            ' Check each character in the cell for erroneous values...
            For y = 1 To stringLength
                charValue = Mid(c.Value, y, 1)
                Select Case charValue
                    Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", _
                         "a", "A", "b", "B", "c", "C", "d", "D", "e", "E", _
                         "f", "F", "g", "G", "h", "H", "i", "I", "j", "J", _
                         "k", "K", "l", "L", "m", "M", "n", "N", "o", "O", _
                         "p", "P", "q", "Q", "r", "R", "s", "S", "t", "T", _
                         "u", "U", "v", "V", "w", "W", "x", "X", "y", "Y", _
                         "z", "Z", ".", ",", "-", " "
                         errorFlag = 0
                    Case Else
                         errorFlag = 1
                        ' Alert user an erroneous character was found...
                        ' Define user dialog parameters
                        Msg = "An erroneous character was found in cell " & c.Address & "."
                        Style = vbOKOnly + vbExclamation
                        Title = "ASCII Character Verification Error"
                        ' Display user dialog
                        Response = MsgBox(Msg, Style, Title)
                        Exit For    ' Exit loop if error found.
                End Select
            Next y
        End If  ' End of erroneous character verifcation loop.
        ' Inform user if no erroneous character is found...
        If errorFlag = 0 Then
            Application.StatusBar = "Cell " & cellCounter & " of " & cellTotal & ", verified as OK..."
        End If
    Next c   ' End of cell data loop.
    ' Reset status bar...
    Application.StatusBar = ""
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    Beep
    ' Alert user verification is complete...
    ' Define user dialog parameters
    Msg = "ASCII character verification is completed."
    Style = vbOKOnly + vbInformation
    Title = "ASCII Character Verification Status"
    ' Display user dialog
    Response = MsgBox(Msg, Style, Title)
End Sub

Hope this helps,
theDude

Thank you for the great macro! Works fantastically, I am really impressed!

How I could I change the macro to check in one specific CELL only?
Just out of curiousity, I would like to implement a little error check when users input new data in a field.

It would be greeat that as soon as they enter the text and press "ENTER", a message pops up alerting them of the error...

Is that possible? Let's say, the text being in cell A2...

Thanks again and big respect to your skills...

Titus

Hi mudraker,

Thank you for the macro, it is exactly what I wanted! The only thing is, there is something that is not working, as the macro is displaying an error even if the characters are all valid (tried with the word "TEST", no quotation marks obviously).

See attached screenshot.. Anything I have done wrong, although I did follow the instructions with regards to inserting the macro in the work sheet...?

Thanks, Titus.

Sorry I posted the wrong version

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim iChar As Integer
   For Each Rng In Target
      If Not Application.Intersect(Target, _
      Range("A2")) Is Nothing Then
         If Rng.Value <> "" Then
            For iChar = 1 To Len(Rng.Value)
               Select Case Mid(Rng.Value, iChar, 1)
               Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", _
                            "a", "A", "b", "B", "c", "C", "d", "D", "e", "E", _
                            "f", "F", "g", "G", "h", "H", "i", "I", "j", "J", _
                            "k", "K", "l", "L", "m", "M", "n", "N", "o", "O", _
                            "p", "P", "q", "Q", "r", "R", "s", "S", "t", "T", _
                            "u", "U", "v", "V", "w", "W", "x", "X", "y", "Y", _
                            "z", "Z", ".", ",", "-", " "
               Case Else
                  MsgBox "Invalid Charactor Entered in " & Rng.Address & Chr(10) & Mid(Rng.Value, iChar, 1)
                  'Rng.ClearContents
                  Exit For
               End Select
            Next iChar
         End If
      End If
   Next Rng
End Sub