Getting Rid of Messy Characters 2

[porto99]

After importing the data into a Access table I need to format (clean) a Memo field as it has some odd characters and cr/lf also.

The code I have been using to do this is shown below, my question is they must be a better way of doing this

Note the input (Instring) is the Memo field to be cleaned.


Private Sub CleanField(Instring As String)
Dim I As Integer
Dim length As Integer

Modified = False
length = Len(Instring)
If length > 0 Then
For I = 0 To length - 1
char = Asc(Mid(Instring, I + 1, 1))
If char < 21 Or char > 126 Then
Outstring = Outstring & " "
Modified = True
intRecChanged = intRecChanged + 1
Else
Outstring = Outstring & Chr(char)
End If
Next I
End If
'Debug.Print Outstring
If Modified Then
' If we have replaced special characters with spaces, then need to remove extra spaces now
Instring = Replace(Instring, " ", " ")
Instring = Replace(Instring, " ", " ")
Instring = Replace(Instring, " ", " ")
Instring = Replace(Instring, " ", " ")
End If
Instring = Outstring

End Sub

 

[Zygor]

Maybe something like this will give you a start in the right direction

Public Function fFixApostrophe(strToSearch As String) As String
Dim strTmp As String
Dim x As Integer
For x = 1 To Len(strToSearch)
If InStr("'", Mid(strToSearch, x, 1)) Then
strTmp = strTmp & "''"
Else
strTmp = strTmp & Mid(strToSearch, x, 1)
End If
Next x
fFixApostrophe = strTmp

End Function

 

[CautionMP]

proto99,
Not really, I've done alot of this and have yet to stumble accross a really 'slick' way to do it.

The only improvement I can see it to 'automate' the conversion from string character to character code using a Byte array.

I'm not sure this is any faster than your method, but the code is shorter.

Sub CleanField(Instring As String)
Dim bytChar() As Byte
Dim I As Integer
Dim Outstring As String

'Load your string into a Byte array, esentially
'an array of Chr() codes
bytChar = Instring

'Cycle trough the array looking at the codes
'*NOTE: a character is 2 bytes, ignore the second
'       by stepping two at a time
For I = 0 To UBound(bytChar) Step 2
  If bytChar(I) > 21 And bytChar(I) < 126 Then
    Outstring = Outstring & Chr(bytChar(I))
  Else
    Outstring = Outstring & " "
  End If
Next I

'Remove blocks of spaces
For I = 5 To 2 Step -1
  Outstring = Replace(Outstring, Space$(I), " ")
Next I

Instring = Outstring

End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Zygor]

Nothing ventured, nothing gained.

I hope you find what you need.

 

[CautionMP]

P.S. I was testing for speed and I think you want this instead:

...
            If char > 21 AND char < 126 Then
                Outstring = Outstring & Chr(char)
            Else
                Outstring = Outstring & " "
                Modified = True
                intRecChanged = intRecChanged + 1
            End If
...

Otherwise it was returning the whole string.

CMP

(GMT-07:00) Mountain Time (US & Canada)