正则表达式匹配年份？

Question

我不熟悉excel vba中的正则表达式，一直在研究有关堆栈溢出的几个问题，在下面的链接“如何在单元格和循环中使用Microsoft excel中的正则表达式（Regex）”中找到了一个很好的问题

这里有一些非常有用的代码，我想我可能会尝试学习，并根据自己的目的进行调整，我正在尝试匹配一个4位数的字符串，代表电子表格上一个单元格中的一年，即“2016年是好的一年”将产生“2016年”。

我使用了发布在那里的问题中的一些稍加修改的代码，它成功地识别出一个字符串包含一年，但是我不确定如何从其余单元格内容中分离和提取该字符串，也就是说，在相邻的单元格中独立运行2016年，我应该做什么更改？

Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("D2:D244")

For Each c In Myrange

    strPattern = "([0-9]{4})" 'looks for (4 consecutive numbers)

    If strPattern <> "" Then
        strInput = c.Value
        strReplace = "$1"

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

        If regEx.Test(strInput) Then
            c.Offset(0, 5) = regEx.Replace(strInput, "$1") 'puts the string in an adjacent cell
        Else
            c.Offset(0, 5) = "(Not matched)"
        End If
    End If
Next
End Sub

Answer 1

user1016274，谢谢，你的评论真的很有帮助，我不得不在上面搜索一下，但我找到了答案

使用正则表达式。Execute（strInput）我设法返回匹配的字符串：

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("D2:D244")

    For Each c In Myrange

        strPattern = "([0-9]{4})" 'looks for (4 consecutive numbers)

        If strPattern <> "" Then
              strInput = c.Value
              strReplace = "$1"

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

            If regEx.Test(strInput) Then
                c.Offset(0, 5) = regEx.Execute(strInput).Item(0).SubMatches.Item(0) 'this was the part I changed
            Else
                c.Offset(0, 5) = "(Not matched)"
            End If
        End If
    Next
 End Sub

Answer 2

您可以显著改进代码，如下所示：

>

Private Sub splitUpRegexPattern()

    Dim regEx As Object
    Dim strPattern As String
    Dim strInput As String
    Dim X
    Dim Y
    Dim lngCnt As Long

    Set regEx = CreateObject("vbscript.regexp")
    X = ActiveSheet.Range("D2:D244").Value2
    Y = X

    strPattern = "\b[0-9]{4}\b" 'looks for (4 consecutive numbers)

    With regEx
        .MultiLine = True
        .Pattern = strPattern

        For lngCnt = 1 To UBound(X)

            If .Test(X(lngCnt, 1)) Then
                Y(lngCnt, 1) = .Execute(X(lngCnt, 1))(0)
            Else
                Y(lngCnt, 1) = "(Not matched)"
            End If
        Next

        Range("D2:D244").Offset(0, 5).Value2 = Y
    End With
End Sub