vba - 在VBA中删除字符串开头和结尾的空格

我已经编写了此函数来删除字符串开头和结尾的空格，为什么不起作用？

Public Function PrepareString(TextLine As String)

    Do While Left(TextLine, 1) = " " ' Delete any excess spaces
        TextLine = Right(TextLine, Len(TextLine) - 1)
    Loop
    Do While Right(TextLine, 1) = " " ' Delete any excess spaces
        TextLine = Left(TextLine, Len(TextLine) - 1)
    Loop

    PrepareString = TextLine

End Function

最佳答案

这个怎么样。请注意Worksheetfunction.Trim的使用，它将删除Application.Trim不会删除的多个空格。

Option Explicit

Dim oRegex As Object

Sub test()
Dim dirtyString As String

    dirtyString = "   This*&(*&^% is_                          The&^%&^%><><.,.,.,';';';  String   "
    Debug.Print cleanStr(dirtyString)
End Sub

Function cleanStr(ByVal dirtyString As String) As String

    If oRegex Is Nothing Then Set oRegex = CreateObject("vbscript.regexp")
    With oRegex
        .Global = True
        'Allow A-Z, a-z, 0-9, a space and a hyphen -
        .Pattern = "[^A-Za-z0-9 -]"
        cleanStr = .Replace(dirtyString, vbNullString)
    End With
    cleanStr = WorksheetFunction.Trim(cleanStr)
End Function

关于vba - 在VBA中删除字符串开头和结尾的空格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12652952/