在vba中修剪前导和尾随空格的功能 [英] Function to trim leading and trailing whitespace in vba

问题描述

我已经检查了很多建议，修改领先和vba中的尾随空格（excel，顺便提一下）。

我已经找到了这个解决方案，但它也修剪了åäö（也是上限），我在正则表达式中太弱了查看原因：

 函数MultilineTrim（Byval TextData）
 Dim textRegExp 
设置textRegExp = new regexp 
 textRegExp.Pattern =\s {0，}（\S {1} [\s，\S] * \S {1}）\s {0，}
 textRegExp.Global = False 
 textRegExp.IgnoreCase = True 
 textRegExp.Multiline = True 
 
如果textRegExp.Test（TextData）然后
 MultilineTrim = textRegExp.Replace（TextData ，$ 1）
 Else 
 MultilineTrim =
如果
结束函数
  

（这是从SO在这里的一个答案，其中useraccount看起来不活动：

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

所以，我如果任何人都可以帮助，会很喜欢（a）这个问题的替代解决方案，或者（b）一个不会剥离（单个）åäö字符的正则表达式/代码的版本。

感谢任何帮助

详细信息：
问题

• 在vba中修剪功能不考虑所有的空格字符（例如标签）。


• 我找到的最佳解决方案是上面的，但它也会删除单个åäö角色。

我的上下文是一个xmlparser在vba中，它获取大量的XML解析。它有时只是从流中获取一个字符，这可能是åäö，然后这个功能完全剥离。

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

FYI：我已经根据答案分享了我所做的一切，见下文。

解决方案

对于正则表达式，我将使用：

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

这将匹配通常的空格字符以及通常在HTML文档中找到的NBSP。

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

  Dim RE为Object，ResultString as String 
设置RE = CreateObject（ vbscript.regexp）
 RE.MultiLine = True 
 RE.Global = True 
 RE.Pattern =^ [\s\xA0] + | [\s\xA0 ] + $
 ResultString = RE.Replace（S，）
  

expla正则表达式：

 在每行的开头和结尾修剪空格
 ------ ----------------------------------------------- 
 
 ^ [\s\xA0] + | [\s\xA0] + $ 
 
选项：^ $换行符
 
匹配此替代方法（仅在此失败时尝试下一个替代方法）«^ [\s\xA0] +»
在行的开头处置位（在字符串的开头或换行符之后） ）«^»
匹配下面列表中的单个字符«[\s\xA0] +»
在一个到无限次之间，尽可能多的次数，根据需要回馈（贪婪）«+»
空格（ASCII空格，制表符，换行符，回车符，垂直制表符，表单进给）«\s»
位置为0xA0（十进制为十进制）的字符字符集«\xA0»
或匹配此替代方法（整个匹配尝试失败，如果这个失败匹配）«[ \\\xA0] + $»
匹配下列列表中存在的单个字符«[\s\xA0] +»
在无限次之间，尽可能多的次数，给出（+）
空格（ASCII空格，标签，换行符，回车符，垂直标签，表单供稿）«\s»
位置为0xA0的字符（160十进制）在字符集«\xA0»
在一行的末尾（在字符串的末尾或换行符之前）的位置«$»
 
创建与RegexBuddy 
  

I have checked quite a few suggestions re trimming leading & trailing whitespace in vba (excel, incidentally).

I have found this solution, but it also trims å ä ö (also caps) and I am too weak in regex to see why:

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

(this is from an answer here at SO, where the useraccount seems inactive:

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

So, I would love if anyone could help with either (a) an alternative solution to the problem or (b) a version of the regexp / code that would not strip out (single) åäö characters.

Thanks for any help!

Details: Problem

• Trim functions in vba do not consider all whitespace chars (tabs, for instance). Some custom trim is needed
• The best solution I found is above, but it also removes single å ä ö characters.

My context is a xmlparser in vba, where it gets chunks of xml to parse. It sometimes just gets a character from the stream, which may be å ä ö, which then this function strips away completely.

I would be happy to clarify or edit this question, of course.

FYI: I have shared exactly what I did based on the answers, see below.

解决方案

For a regex I would use:

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

This will match the "usual" whitespace characters as well as the NBSP, commonly found in HTML documents.

VBA Code would look something like below, where S is the line to Trim:

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, "")

And an explanation of the regex:

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

这篇关于在vba中修剪前导和尾随空格的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！