string - VBA:如何从数据中删除不可打印的字符

Question

我需要以编程方式删除不可打印的字符，例如:

制表符 - 字符 (9)换行符 - char(10)回车 - 字符 (13)数据链接转义 - char(16)

我启动了一个通用函数，该函数将从 ms 访问表单字段的 lost_focus 事件中调用。

我还没有想出如何识别字符串何时包含不需要的字符。

    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

这些是我在测试时一直使用的 2 个字符串:

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

Answer 1

当我搜索要使用的快速功能时，这是谷歌的最高结果，我有一个很好的旧谷歌，但没有真正完全解决我的问题。

主要问题是即使没有问题，所有这些函数都会触及原始字符串。这会减慢速度。

我重写了它，以便仅在出现错误字符时进行修改，还扩展到所有不可打印的字符和超出标准 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