vba 删除vba中字符串开头和结尾的空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12652952/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete blanks from start and end of a string in vba
提问by Chris
I've written this function to delete blanks from the start and the end of a string, any ideas why it isn't working?
我已经编写了这个函数来删除字符串开头和结尾的空白，有什么想法为什么它不起作用？
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
采纳答案by user3357963
How about this. Note the use of Worksheetfunction.Trim which removes multiple whitespaces which Application.Trim does not.
这个怎么样。请注意 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
回答by danielpiestrak
I tested your function and it works fine on my machine.
我测试了你的功能，它在我的机器上运行良好。
You can use the built in Trim()function that does this for you instead of creating a UDF that does the same thing.
您可以使用Trim()为您执行此操作的内置函数，而不是创建执行相同操作的 UDF。
Trim(TextLine)
Reference: http://www.techonthenet.com/excel/formulas/trim.php
回答by Siddharth Rout
Why not this?
为什么不是这个？
Public Function PrepareString(TextLine As String)
PrepareString = Trim(TextLine)
End Function
Alexandre/slaver113 are absolutely correct that it doesn't make any sense to wrap a built-in function inside a UDF. The reason why I had done the above is to point out how to make your UDF work. In real life scenario, I would never use the UDF in such a way. :)
Alexandre/slaver113 是绝对正确的，将内置函数包装在 UDF 中没有任何意义。我这样做的原因是为了指出如何使您的 UDF 工作。在现实生活中，我绝不会以这种方式使用 UDF。:)
