excel - 非打印字符的修剪功能

Question

修剪功能仅修剪空格。我需要一个修剪所有非打印字符的功能。
我的代码...

Private Sub CleanUpData()
    LastRow = Application.CountA(ActiveSheet.Range("A:A"))
    For CurrentRow = 2 To LastRow
        Cells(CurrentRow, 1) = Trim(Cells(CurrentRow, 1))
        Cells(CurrentRow, 2) = Trim(Cells(CurrentRow, 2))
        Cells(CurrentRow, 3) = Trim(Cells(CurrentRow, 3))
        Cells(CurrentRow, 4) = Trim(Cells(CurrentRow, 4))
    Next CurrentRow
End Sub

...什么也没做。

Answer 1

尝试这个:

 Public Function TrimComplete(ByVal sValue As String) As _
        String

        Dim sAns As String
        Dim sWkg As String
        Dim sChar As String
        Dim lLen As Long
        Dim lCtr As Long

        sAns = sValue
        lLen = Len(sValue)

        If lLen > 0 Then
            'Ltrim
            For lCtr = 1 To lLen
                sChar = Mid(sAns, lCtr, 1)
                If (Asc(sChar) > 32) and (Asc(sChar) < 127) Then Exit For
            Next

            sAns = Mid(sAns, lCtr)
            lLen = Len(sAns)

            'Rtrim
            If lLen > 0 Then
                For lCtr = lLen To 1 Step -1
                    sChar = Mid(sAns, lCtr, 1)
                    If (Asc(sChar) > 32) and (Asc(sChar) < 127) Then Exit For
                Next
            End If
            sAns = Left$(sAns, lCtr)
        End If

        TrimComplete = sAns

    End Function

摘自

Link to source