我正在寻求一些建议。
我正在尝试在 Visual Basic for Excel 中使用正则表达式(参考已设置的 Microsfot VBScript Regular Expresions 5.5)。
我只希望这个函数测试我的正则表达式\d\d\d\d\d
(查找任意 5 个连续的整数),如果为真,则给出匹配的值。
例如,如果我有字符串“aaaaa 12345 bbb”,我希望函数给出“12345”。这似乎很容易,但……对我来说并不容易。
这是我目前的代码:
Function EXTRACT_CP(cell_with_text As Range) As String
Dim regEx As New RegExp
Dim strexpresion As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strexpresion = "\d\d\d\d\d"
strInput = UCase(cel_with_text.Value)
regEx.Pattern = strexpresion
If regEx.Test(strInput) Then
‘THIS LINE OBVIOUSLY FAILS BUT I DON’T KNOW WHAT TO PUT
strOutput = regEx.Replace(strInput, "\d\d\d\d\d")
End If
EXTRACT_CP = strOutput
End Function
只是想知道如何获取任何正则表达式的匹配值。
答案1
五位连续的数字?
添加变量:
Dim colMatches As Object
Dim ix As Long
Dim separator As String
有效的 RegEx:
regEx.Pattern = "(\d{5})"
改变:
strOutput = regEx.Replace(strInput, "\d\d\d\d\d")
到:
Set colMatches = regEx.Execute(strInput)
并迭代你唯一的匹配(\d{5})
〜colMatches.Item(0)
子匹配:
separator = "|"
strOutput = ""
For ix = 0 to colMatches.Item(0).submatches.count - 1
strOutput = strOutput + colMatches.Item(0).submatches.Item(ix) + separator
代码是在没有 VBA 编辑器的情况下编写的,因此可能需要进行调整。