CODEWITHSUNDEEP
stringvbareplacems-access-2007
erasmo carlos
erasmo carlos

Reputation: 682

VBA: How to remove non-printable characters from data

I need to remove programmatically non printable characters such as:

tabs - char(9) line breaks - char(10) carriage return - char(13) data link escape - char(16)

I started a generic function that will be called from the lost_focus event of the ms access form field.

I have not figured out how to identify when the string contains the unwanted characters.

    Function RemoveNonPrintableCharacters(ByVal TextData) As String

        Dim dirtyString As String
        Dim cleanString As String
        Dim iPosition As Integer

        If IsNull(TextData) Then
            Exit Function
        End If

        dirtyString = TextData
        cleanString = ""

        For iPosition = 1 To Len(dirtyString)
            Select Case Asc(Mid(dirtyString, iPosition, 1))
                Case 9    ' Char(9)
                Case 10   ' Char(10)
                Case 13   ' Char(13)
                Case 16   ' Char(16)
                Case Else ' Add character to clean field.
                    cleanString = cleanString & Mid(dirtyString, iPosition, 1)
            End Select
        Next

        RemoveNonPrintableCharacters = cleanString

    End Function

These are 2 strings I have been using whilst testing:

This line,    has       multiple,     tabs       that   need to be removed


This line, has multiple,     
line
breaks
that
need to be removed

This line,    has       multiple,     tabs       that   need to be removed
And
Also contains
multiple,     
line
breaks
that
need to be  removed

Upvotes: 3

Views: 24957

Answers (10)

Vladimir
Vladimir

Reputation: 1

If there are special characters, then replace them with spaces

If InStr(TextData, Chr(9)) > 0 Then TextData = Replace(TextData, Chr(9), Chr(32))
If InStr(TextData, Chr(10)) > 0 Then TextData = Replace(TextData, Chr(10), Chr(32))
If InStr(TextData, Chr(13)) > 0 Then TextData = Replace(TextData, Chr(13), Chr(32))
If InStr(TextData, Chr(16)) > 0 Then TextData = Replace(TextData, Chr(16), Chr(32))

Upvotes: 0

Алексей Р
Алексей Р

Reputation: 7627

It can be solved by RegEx (add MS VBScript Regular Expression in Tools - References in VBE):

Function NormalString(text As String, Optional filler = vbNullString) As String
    Dim re As New RegExp
    With re
        .Pattern = "([\x00-\x1F\xA0])"
        .Global = True
        text = .Replace(text, filler)
    End With
    NormalString = text
End Function

Upvotes: 0

Paweł Jamiołkowski
Paweł Jamiołkowski

Reputation: 81

When Unicode characters appear, the codes presented here should be modified. My proposal includes unrecognized characters by the program:

Public Function Clean_NonPrintableCharacters(Str As String) As String

    'Removes non-printable characters from a string

    Dim cleanString As String
    Dim i As Integer

    cleanString = Str
    
    For i = Len(cleanString) To 1 Step -1

        If Chr(Asc(Mid(cleanString, i, 1))) <> Mid(cleanString, i, 1) Then
        cleanString = Left(cleanString, i - 1) & Mid(cleanString, i + 1)
        End If
        
    Next i

    Clean_NonPrintableCharacters = WorksheetFunction.Clean(cleanString)

End Function

Upvotes: 2

Dave Scott
Dave Scott

Reputation: 153

This is the top google result when I search for a quick function to use, I've had a good old google but nothing that solves my issue fully has really come up.

The main issue is that all of these functions touch the original string even if there's no issue. Which slows stuff down.

I've rewritten it so that only amends if bad character, also expanded to all non-printable characters and characters beyond standard ascii.

Public Function Clean_NonPrintableCharacters(Str As String) As String

    'Removes non-printable characters from a string

    Dim cleanString As String
    Dim i As Integer

    cleanString = Str

    For i = Len(cleanString) To 1 Step -1
        'Debug.Print Asc(Mid(Str, i, 1))

        Select Case Asc(Mid(Str, i, 1))
            Case 1 To 31, Is >= 127
                'Bad stuff
                'https://www.ionos.com/digitalguide/server/know-how/ascii-codes-overview-of-all-characters-on-the-ascii-table/
                cleanString = Left(cleanString, i - 1) & Mid(cleanString, i + 1)

            Case Else
                'Keep

        End Select
    Next i

    Clean_NonPrintableCharacters = cleanString

End Function

Upvotes: 4

Lyspon
Lyspon

Reputation: 31

It seems as if this should be much simpler, using the Excel Clean function. The following also works:

myString = Worksheets("Sheet1").Range("A" & tRow).Value 
myString = Application.WorksheetFunction.Clean(myString)

You can also use other normal and home-grown Excel functions:

myString = Application.WorksheetFunction.Trim(myString)

Still haven't gotten the Substitute function to work in this way, but I'm working on it.

Upvotes: 1

Bill
Bill

Reputation: 1

This works to remove Non-printing Characters from the right side of the string only and do not replace the characters with spaces.

Function fRemoveNonPrintableCharacters(ByVal TextData) As String
Dim dirtyString As String
Dim cleanString As String
Dim iPosition As Integer

If IsNull(TextData) Then
    Exit Function
End If

dirtyString = TextData
cleanString = ""

For iPosition = Len(dirtyString) To 1 Step -1
    Select Case Asc(Mid(dirtyString, iPosition, 1))
        Case 9, 10, 13, 16, 32, 160
            cleanString = cleanString
            Case Else
            cleanString = Left(dirtyString, iPosition)
            Exit For

    End Select
Next

fRemoveNonPrintableCharacters = cleanString

End Function

Upvotes: 0

RyanL
RyanL

Reputation: 1276

I'm replacing non-printable characters with a space character chr(32) but you can alter this to suit your needs.

Function RemoveNonPrintableCharacters(ByVal TextData) As String
Dim sClean$

sClean = Replace(TextData, Chr(9), Chr(32))
sClean = Replace(sClean, Chr(10), Chr(32))
sClean = Replace(sClean, Chr(13), Chr(32))
sClean = Replace(sClean, Chr(16), Chr(32))

RemoveNonPrintableCharacters = sClean

End Function

Upvotes: 0

arif
arif

Reputation: 55

'first you need to find a character

YourStr = "Bla bla bla..."

if instr(YourStr, chr(10)) > 0 then

NewStr = Replace(YourStr, Chr(10),"")

end if

Upvotes: 0

Freddie
Freddie

Reputation: 269

A = Chr(09) & "Cat" & Chr(10) & vbcrlf

A = Replace(A, Chr(10))
A = Replace(A, Chr(13))
A = Replace(A, Chr(09))

Msgbox A

This is how one normally does it.

Your code is creating a lot of implicit variables.

Upvotes: 2

RyanL
RyanL

Reputation: 1276

    Function RemoveNonPrintableCharacters(ByVal TextData) As String

    Dim dirtyString As String
    Dim cleanString As String
    Dim iPosition As Integer

    If IsNull(TextData) Then
        Exit Function
    End If

    dirtyString = TextData
    cleanString = ""

    For iPosition = 1 To Len(dirtyString)
        Select Case Asc(Mid(dirtyString, iPosition, 1))
            Case 9, 10, 13, 16
                cleanString = cleanString & " "
                Case Else
                cleanString = cleanString & Mid(dirtyString, iPosition, 1)
        End Select
    Next

    RemoveNonPrintableCharacters = cleanString

End Function

Upvotes: 1

Related Questions