regex - vba中修剪前导和尾随空格的函数

Question

我检查了很多关于在 vba 中修剪前导和尾随空格的建议(顺便说一下，excel)。

我找到了这个解决方案，但它也修剪了 å ä ö (也是大写)，而且我在正则表达式方面太弱，看不出原因:

Function MultilineTrim (Byval TextData)
    Dim textRegExp
    Set textRegExp = new regexp
    textRegExp.Pattern = "\s{0,}(\S{1}[\s,\S]*\S{1})\s{0,}"
    textRegExp.Global = False
    textRegExp.IgnoreCase = True
    textRegExp.Multiline = True

    If textRegExp.Test (TextData) Then
      MultilineTrim = textRegExp.Replace (TextData, "$1")
    Else
      MultilineTrim = ""
    End If
End Function

(这是来自 SO 的答案，其中用户帐户似乎处于非事件状态:

https://stackoverflow.com/a/1606433/3701019)

因此，如果有人可以帮助解决(a)问题的替代解决方案或(b)不会删除(单个)åäö字符的正则表达式/代码版本，我会很高兴。

感谢您的帮助!

详细信息:问题

• vba 中的 Trim 函数不考虑所有空白字符(例如制表符)。需要一些定制装饰
• 我找到的最佳解决方案如上所述，但它也删除了单个 å ä ö 字符。

我的上下文是 vba 中的 xmlparser，它获取要解析的 xml block 。有时它只是从流中获取一个字符，可能是 å ä ö，然后该函数将其完全剥离。

当然，我很乐意澄清或编辑这个问题。

仅供引用:我已经根据答案准确分享了我所做的事情，请参见下文。

Answer 1

对于正则表达式，我会使用:

^[\s\xA0]+|[\s\xA0]+$

这将匹配 HTML 文档中常见的“常见”空白字符以及 NBSP。

VBA 代码如下所示，其中 S 是要修剪的行:

Dim RE as Object, ResultString as String
Set RE = CreateObject("vbscript.regexp")
RE.MultiLine = True
RE.Global = True
RE.Pattern = "^[\s\xA0]+|[\s\xA0]+$"
ResultString = RE.Replace(S, "")

以及正则表达式的解释:

Trim whitespace at the start and the end of each line
-----------------------------------------------------

^[\s\xA0]+|[\s\xA0]+$

Options:  ^$ match at line breaks

Match this alternative (attempting the next alternative only if this one fails) «^[\s\xA0]+»
   Assert position at the beginning of a line (at beginning of the string or after a line break character) «^»
   Match a single character present in the list below «[\s\xA0]+»
      Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
      A “whitespace character” (ASCII space, tab, line feed, carriage return, vertical tab, form feed) «\s»
      The character with position 0xA0 (160 decimal) in the character set «\xA0»
Or match this alternative (the entire match attempt fails if this one fails to match) «[\s\xA0]+$»
   Match a single character present in the list below «[\s\xA0]+»
      Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
      A “whitespace character” (ASCII space, tab, line feed, carriage return, vertical tab, form feed) «\s»
      The character with position 0xA0 (160 decimal) in the character set «\xA0»
   Assert position at the end of a line (at the end of the string or before a line break character) «$»

Created with RegexBuddy