更新时间:2023-01-22 19:03:28
您可以使用正则表达式:
You can do this with regular expressions:
Option Explicit
Function trimWhiteSpace(s As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.Pattern = "^\s*(\S.*\S)\s*"
trimWhiteSpace = .Replace(s, "$1")
End With
End Function
正则表达式的解释
^\s*(\S.*\S)\s*
选项:区分大小写;^ $匹配行间的中断
Options: Case sensitive; ^$ match at line breaks
^
\ s *
\ S
.*
$ 1
使用 RegexBuddy
另一方面,如果您要避免使用正则表达式,并且您唯一的前导/结尾空白"字符是 space
, tab
和换行
,和,如果唯一的内部"空格字符是空格
,则可以使用:
On the other hand, if you want to avoid regular expressions, and if your only leading/trailing "white-space" characters are space
, tab
and linefeed
, AND if the only "internal" white space characters are the space
, you could use:
Function trimWhiteSpace(s As String) As String
trimWhiteSpace = Trim(Replace(Replace([a1], vbLf, ""), vbTab, ""))
End Function
请注意,VBA Trim函数(与工作表函数不同)仅删除前导和尾随空格,并使内部空间保持不变.但是,如果您需要保留字符串中的 tab
,这将无法正常工作.
Note that the VBA Trim function (unlike the worksheet function), only removes leading and trailing spaces, and leaves internal spaces unchanged. But this won't work if you have tab
's within the string that need to be preserved.
以上任何一种都可以合并到您的宏中.
Either of the above can be incorporated into your macro.