arrays - 具有数学属性的 Excel VBA 字符串转换

Question

我有一种好奇心，它激起了我的兴趣并难倒了我。我有一种情况，我有一堆字符串，有些带有数学符号，有些只是字符。其中一些字符串很长，有些则不是，但是，我想在任何具有数学符号(特别是 +、-)的字符串的完整字符串之后添加一个 Chr(10)，然后继续读取字符串下一个数学符号，并在它之前插入一个 Chr(10)。

最终结果应如下所示:

10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123

Converts to: 

10+20+30+40  
50+60+70+80 
123a 123 123b 345 123c 123 123d 123 
90+100+110+120 
123 123 231 123

在方程式没有空格的情况下，普通字母(可能是数字和字母的混合)可以有空格，但在找到数学符号时会分开。

我在网上寻找一些线索，我想我很接近弄清楚了。看来是拆分、合并，还是Right()、Left()，那么Len()函数就是答案。

 If Len(SearchString) = "+" Or Len(SearchString) = "-" Then
    SearchString = Left(SearchString, Chr(10))
 End If

然而这不起作用。我可能不太了解 Len、Left 和 Right 函数，但我非常肯定 SearchString 是正确的做法。还值得注意的是，我提供的 If/End If 语句确实有效，它不会返回任何错误，但它并没有按照我的意愿行事。

Answer 1

这是使用正则表达式的另一种方式。我使用 .Replace方法

Option Explicit
Sub splitter()
    Dim S As String, RE As Object
    Const spat As String = "((?:(?:\w+[+-])+)+\w+)(?=\s|$)|(?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)"
    Dim sRes As Variant

S = "10+20+30+40 50+60+70+80 123a 123 123b 345 123c 123 123d 123 90+100+110+120 123 123 231 123"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = spat
    sRes = .Replace(S, vbLf & "$1$2")
    MsgBox sRes
End With

End Sub

如何应用这取决于您的数据设置；浏览您的原始数据并在某处吐出结果。

分路器

((?:(?:\w+[+-])+)+\w+)(?=\s|$)|(?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)

选项:不区分大小写； ^$ 在换行符处不匹配

Match this alternative ((?:(?:\w+[+-])+)+\w+)(?=\s|$)

Match the regex below and capture its match into backreference number 1 ((?:(?:\w+[+-])+)+\w+)

Match the regular expression below (?:(?:\w+[+-])+)+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match the regular expression below (?:\w+[+-])+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match a single character that is a “word character” \w+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match a single character present in the list below [+-]

The literal character “+” +

The literal character “-” -

Match a single character that is a “word character” \w+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Assert that the regex below can be matched starting at this position (positive lookahead) (?=\s|$)

Match this alternative \s

Match a single character that is a “whitespace character” \s

Or match this alternative $

Assert position at the very end of the string $

Or match this alternative (?:^|\s)((?:\w+\s+)+\w+)(?=\s|$)

Match the regular expression below (?:^|\s)

Match this alternative ^

Assert position at the beginning of the string ^

Or match this alternative \s

Match a single character that is a “whitespace character” \s

Match the regex below and capture its match into backreference number 2 ((?:\w+\s+)+\w+)

Match the regular expression below (?:\w+\s+)+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match a single character that is a “word character” \w+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match a single character that is a “whitespace character” \s+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Match a single character that is a “word character” \w+

Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

Assert that the regex below can be matched starting at this position (positive lookahead) (?=\s|$)

Match this alternative \s

Match a single character that is a “whitespace character” \s

Or match this alternative $

Assert position at the very end of the string $

1 美元 2 美元

Insert a line feed ␊

Insert the text that was last matched by capturing group number 1 $1

Insert the text that was last matched by capturing group number 2 $2

创建于 RegexBuddy