Regular expression to match year?

Question

I'm new to regular expressions in excel vba, been looking at a few questions about it on stack overflow, found a great one at the following link "How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops"

There was some very useful code here that I thought I might try to learn and adapt for my purposes, I'm trying to match a 4 digit string representing a year from a cell on a spreadsheet ie. "2016 was a good year" would yield "2016".

I used some slightly altered code from that question posted there and it manages to recognize that a string contains a year, however I'm not sure how to separate and extract the string from the rest of the cell contents, ie. getting 2016 on it's own in an adjacent cell, any changes I should make?

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

The `regEx.Execute()` functions returns a match object which will contain a number of submatches, one for each capturing group `(pattern)` in your pattern. You'll find many descriptions of this e.g. on SO. Also, move your pattern assignment out of the loop, it's invariant. Lastly, to match a year you would use `([12][0-9]{3})` to match only the last and the current millenium. — user1016274, Nov 07 '15 at 14:11

score 3 · Answer 1 · edited Sep 27 '18 at 17:13

You could significantly improve your code as below:

Use variant arrays rather than a range
Move the RegExp out of the loop (you are setting it the same way for each cell)

Your RegExp parameters can be reduced for what you want (minor).

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

user3545370 · Accepted Answer · 2015-11-09T16:12:02.913

user1016274, thanks, your comment really helped, had to do some searching on it, but I found the answer

using regEx.Execute(strInput) I managed to return the string matched:

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

Would you please edit your answer and add the final code, for others researching the same problem in the future? Then you can mark your answer as 'the answer'. — user1016274, Nov 07 '15 at 14:49

Regular expression to match year?

2 Answers2