RegEx模式选择正确的子字符串，但在运行宏vba时抛出错误。

Question

在带有正则表达式的两个字符串中，在逗号,前面加上一个[ (一个方括号)和? (一个问号)之后，我试图删除所有内容。

我有这样的输入：

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - How strongly do you value your relationship with [Field-2]?

和

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - During the Clinical Scholars Program, with [Field-2], have you partnered new project(s) other than your team's Wicked Problem Impact Project?

因此，我希望删除第一个字符串中的?和第二个字符串中的下面一个字符串

, have you partnered new project(s) other than your team's Wicked Problem Impact Project?

我想以

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - How strongly do you value your relationship with [Field-2]

和

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - During the Clinical Scholars Program, with [Field-2]

我有(?<=]),\s*([^,])+|\?

这个模式似乎是在捕捉我想要的

但是当我运行宏时，我得到了Method 'Replace' of object 'IRegEep2' failed

https://regex101.com/r/c9lDYD/1

我使用宏运行了许多其他正则表达式模式，没有问题，所以不确定问题是什么。

Sub findReplace()
Dim outArray As Variant
Dim regEx As New RegExp
Dim ws As Worksheet
Dim i As Long

  Dim strPattern As String: strPattern = "(?<=]),\s*([^,])+|\?"
  Dim strReplace As String: strReplace = ""
        
  With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
 End With
    
 Set ws = ThisWorkbook.Sheets("Helper_1Filted")

 With ws.Range("K1:K50")
    
      outArray = .value
      For i = 1 To UBound(outArray, 1)
          outArray(i, 1) = regEx.Replace(outArray(i, 1), strReplace)
      Next i
        
      .value = outArray
        
  End With
    
End Sub

Answer 1

我认为vba中不支持查找，但是如果问号应该在匹配逗号和方括号之间的部分之后出现，则可以使用捕获组而不需要替换|。

当使用替换|时，问号将在字符串中的任何位置匹配。

您可以使用捕获组和否定字符类 [^。

在替换使用组1 $1

(,[^\]\[,]*\[[^\]\[]*])[^?]*\?

• ( Capture group 1
  • ,匹配逗号
  • [^\]\[,]*匹配0+乘以除逗号、[或]以外的任何字符
  • 来自\[[^\]\[]*]的[...]匹配

• )闭组1
• [^?]*匹配0+乘以除问号以外的任何字符
• \?匹配问号

Regex演示

或者具有可选捕获组的较短版本：

(\])(?:,\s*[^,]+)?\?

Regex演示

Answer 2

检查后，可能当前的VBA regex库5.5不支持代码中的回调函数，因为在进行测试时会出现警告

?<=]

通过修改它，它的工作方式是替换你的例子中的问号，虽然其他句子我可能不会100%肯定，但我只更改开头部分，fyi。

(^]),\s*([^,])+|\?