regex - Excel 正则表达式查询返回空数据

Question

我正在使用 following VBA code from a related question在我的 Excel 电子表格中，当我在单元格中使用它时，它总是失败(不返回任何内容)。即使我在函数调用中的字符串文字上调用它(即 =RegexExtract("ABC1_DEF","ABC[0-9]") )，它仍然会失败。 I've enabled the "Microsoft Visual Basic Regular Expressions 5.0" feature in the MSVBA application,所以我不确定为什么这些结果总是空的。我该如何解决这个问题？

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional separator As String = ", ") As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & (separator & allMatches.Item(i).submatches.Item(j))
    Next
Next

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(separator))
End If

RegexExtract = result

End Function

编辑

我试过 yet another function from a separate question , 它只返回 #VALUE! :

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String) As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
RegexExtract = allMatches.Item(0).submatches.Item(0)

End Function

Answer 1

请注意，您正在尝试访问 .Submatches存储捕获组值，但您尚未在模式中定义任何捕获组。

如果您使用 (ABC[0-9])您将获得与当前功能的匹配。否则，请访问 allMatches.Item(i)获取完整匹配值并丢弃代码以获取捕获的组。