Trim函数不会删除空格-VBA Excel

Question

我看到了一些与这个问题相关的主题，不幸的是，它们都没有帮助。Trim根本不会删除前面或后面的空格。。。

宏应该通过列"；F"；ad修剪了所有的边，目前id确实通过了列，在MsgBox的帮助下，我看到它正确地获得了单元格中的所有值，但实际的修剪不起作用。

Sub trimAllTrends()
Dim i As Integer
Dim allInLastRow As Long
Dim allInWs As Worksheet
Dim myString As String
Set allInWs = ThisWorkbook.Worksheets("All Trends")
allInLastRow = allInWs.Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To allInLastRow

myString = allInWs.Cells(i, 6).Value
'MsgBox (myString)
'WorksheetFunction.Trim (allInWs.Cells(i, 6))
'allInWs.Cells(i, 6).Value = LTrim(allInWs.Cells(i, 6).Value)
'.Cells(i, "F").Value = Application.Trim(.Cells(i, "F").Value)
WorksheetFunction.Trim (myString)

Next i

End Sub

非常感谢您的帮助！

提前谢谢！

Answer 1

我不得不说，我发现Trim()函数有点有限，尤其是从文字处理或页面设计应用程序导入数据时。因此，每个人在评论中提出的观点都是好的。

如果您感兴趣，我使用自己的TrimWhitespace()函数。毫无疑问，有更快的方法可以做到这一点，但我发现这一种适合我的目的：

Public Function TrimWhitespace(txt As String) As String
Dim i As Long, j As Long, c As Long
Dim startPos As Long, endPos As Long
Dim whitespaces As Variant
Dim isWhitespace As Boolean

' List of whitespace characters.
whitespaces = Array( _
&H9, &HA, &HB, &HC, &HD, &H20, &H85, &HA0, _
&H1680, &H2000, &H2001, &H2002, &H2003, &H2004, &H2005, &H2006, _
&H2007, &H2008, &H2009, &H200A, &H2028, &H2029, &H202F, &H205F, _
&H3000, &H180E, &H200B, &H200C, &H200D, &H2060, &HFEFF)

' Find the first non-whitespace.
For i = 1 To Len(txt)
c = Asc(Mid(txt, i, 1))
isWhitespace = False
For j = LBound(whitespaces) To UBound(whitespaces)
If c = whitespaces(j) Then
isWhitespace = True
Exit For
End If
Next
If Not isWhitespace Then
startPos = i
Exit For
End If
Next

' If there's no start position, return an empty string.
If startPos = 0 Then Exit Function

' Find the last non-whitespace.
For i = Len(txt) To startPos Step -1
c = Asc(Mid(txt, i, 1))
isWhitespace = False
For j = LBound(whitespaces) To UBound(whitespaces)
If c = whitespaces(j) Then
isWhitespace = True
Exit For
End If
Next
If Not isWhitespace Then
endPos = i
Exit For
End If
Next

TrimWhitespace = Mid(txt, startPos, endPos - startPos + 1)
End Function

这里有一些测试代码来演示它：

Public Sub RunMe()
Dim txt1 As String, txt2 As String

txt1 = Chr(32) & Chr(160) & Chr(9) & "abc" & Chr(32) & Chr(160) & Chr(9)
txt2 = Chr(32) & Chr(160) & Chr(9) & "xyz" & Chr(32) & Chr(160) & Chr(9)
txt1 = Trim(txt1)
txt2 = TrimWhitespace(txt2)

Debug.Print "TRIM RESULTS"
Debug.Print "============"
Debug.Print "Trim()"
Debug.Print "------"
Debug.Print "Trimmed: |" & txt1 & "|"
Debug.Print "Desired: |abc|"
Debug.Print
Debug.Print "TrimWhitespace()"
Debug.Print "------------------------"
Debug.Print "Trimmed: |" & txt2 & "|"
Debug.Print "Desired: |xyz|"
End Sub