VBA - 正则表达式和替换

Question

我对 VBA 还很陌生。下面是我的代码，用于识别和删除仅出现在日期之后而不是文本之后的那些点。但它似乎不起作用。

Sub simpleRegexSearch()

    Dim strPattern As String: strPattern = "[0-9]+[\.]"
    Dim strReplace As String: strReplace = "\."
    Dim myreplace As Long
    Dim strInput As String
    Dim Myrange As Range

    Set regEx = CreateObject("VBScript.RegExp")
    Set Myrange = ActiveSheet.Range("A1")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.TEST(strInput) Then
                 Myrange.Value = (regEx.Replace(strInput, strReplace))

            End If
        End If
    Next

    Set regEx = Nothing

End Sub

我正在处理的列中的两行样本是：-

08-02-18. BM sent email to Matt with IM. 15-02-18. Left voice message for Matt today.
08-02-18. BM sent email with IM. 15-2-18. BM spoke to Adam. He is looking at the IM. 16-2-18. Further discussions with Adam today. Looking to develop an office asset with Childcare.

期望的输出是：-

08-02-18 BM sent email to Matt with IM. 15-02-18 Left voice message for Matt today.
08-02-18 BM sent email with IM. 15-2-18 BM spoke to Adam. He is looking at the IM. 16-2-18 Further discussions with Adam today. Looking to develop an office asset with Childcare.

请帮我修改一下其中的内容。

Answer 1

可以进行许多更改来改进您的通用代码。但就正则表达式而言，要使其按您的需要工作，请更改模式并替换字符串

Dim strPattern As String: strPattern = "([0-9]+)[\.]"
Dim strReplace As String: strReplace = "$1"

正则表达式和替换字符串的解释

([0-9]+)\.

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

• 匹配下面的正则表达式，并将其匹配捕获到反向引用编号 1 中 ([0-9]+)
  • 匹配“0”至“9”范围内的单个字符 [0-9]+
    • 一次至无限次之间，尽可能多次，根据需要回馈（贪婪） +
• 逐字匹配字符“。” \.

1 美元

• 插入最后匹配到的捕获组编号 1 的文本 $1

创建于正则表达式好友

Answer 2

[] 是字符范围。因此 strpattern = “([0-9]+)。” 且 strReplace = “$1”。