Remove all non standard characters from text (1 Viewer)

[gwill23]

I searched and searched for something that would remove non-standard characters from my data and here is what I came up with. I wanted to post it so someone else looking for similar functionality could find it.

Background. We are migrating from Goldmine to Microsoft CRM Online. When moving notes, activities, and email there are lots of opportunities for people to add odd characters to the larger text fields. CRM uploads will fail if there are any unrecognized characters in the data at all. After several bubble type trouble shooting ventures by halving a table and uploading them both until I was down to about 10 records I could import into word and look for the special character I knew I had to find something new.

After searching and searching I found a piece of code that would allow me to remove unwanted characters but I couldn't find a piece of code that would just go and remove them all.

After finally looking at a table of ASCII values I realized the solution was simple I just needed to open my eyes. By incorporating for next loops to loop through the ASCII values I am able to check for every character possible. I then used a case statement to exclude the ASCII values that I didn't want it to do anything with and ta-da. All my troubled characters are gone. Thanks to those who helped get me started with the string replacement code.

Public Function RemoveBadChar(FromString As Variant) As Variant
Dim i As Integer
   If IsNull(FromString) Then 'handle Null values
     RemoveBadChar = Null
     Exit Function
   End If
   Dim strTemp As String
   strTemp = FromString
    For i = 1 To 31
        Select Case i
            Case 9, 10, 13
                ' do nothing
            Case Else
            strTemp = Replace(strTemp, Chr(i), "  ")
        End Select
    Next
     For i = 127 To 255
        strTemp = Replace(strTemp, Chr(i), "  ")
    Next
   While InStr(strTemp, "  ") > 0
     strTemp = Replace(strTemp, "  ", " ")
   Wend
   strTemp = Trim(strTemp)
   RemoveBadChar = strTemp
 End Function

I use this code to cycle through each and every field, in each record of a table to make sure I get them all.

Function searchforbadstrings(tblName As String) As String
Dim rs1 As New ADODB.Recordset
Dim strSQLrs1 As String
Dim i As Integer
Dim strNewString As Variant
Dim intprog As Long
DoCmd.OpenForm "frm_progress"
intprog = 0
 strSQLrs1 = "SELECT " & tblName & ".* FROM " & tblName & ";"
 
rs1.Open strSQLrs1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
 
Do While Not rs1.EOF
    If rs1.AbsolutePosition = 8700 Then
        MsgBox "It will clear the MaxLocksPerFIle"
    End If
     intprog = intprog + 1
    Forms!frm_progress!prog.Caption = "Search for Bad Strings in " & tblName & " Is Processing " & intprog & " records of " & rs1.RecordCount
    Pause 0.01
     For i = 1 To rs1.Fields.Count - 2
    
        strNewString = RemoveBadChar(rs1.Fields(i).Value)
        If strNewString <> rs1.Fields(i).Value Then
            rs1.Fields(i).Value = strNewString
        Else
            'do nothing
        End If
    Next
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
 DoCmd.Close acForm, "frm_progress"
End Function

It does take some time so I have a progress form that keeps me occupied while I wait. You can always take that out. While I am sure this is not the cleanest or simplest way to do it. It works great for me.

Have fun!:banghead: