正则表达式匹配年份？-Java 学习之路

我是excel vba中的正则表达式的新手，在堆栈溢出时查看了一些关于它的问题，在以下链接中发现了一个很好的问题"How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops"

这里有一些非常有用的代码，我认为我可能会尝试学习并适应我的目的，我试图在电子表格上匹配一个代表一年的4位数字符串，即 . “ 2016 was a good year " would yield " 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

2 回答

您可以显着改善您的代码，如下所示：

使用变量数组而不是范围
将 RegExp 移出循环（您为每个单元设置相同的方式）
您的 RegExp 参数可以减少您想要的（次要） .

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

回复于 2024-05-19T08:05:06+08:00

user1016274，谢谢，你的评论确实有帮助，不得不做一些搜索，但我找到了答案

使用regEx.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

回复于 2024-05-19T08:05:06+08:00

正则表达式匹配年份？

2 回答

相关问题