Pattern match count in excel (regex & vba)

Question

I have an Office 2007 .XLSX file containing over 5000 records like the below (a single cell with multiple rows of text). The issue: on an adjacent cell, put count of incidents from the cell. Looking at the cell data for A1 you can see 3 incidents:

Cell A1:

1/15/2013 1:30:11 AM Userx
Had to reboot system
1/15/2013 1:32:11 AM Userx
System running finished rebooting and appears to be working
11/15/2013 12:30:11 AM Userx
System hung again

The problem is that the date value isn't consistent. The day, month, and hour can be single or double digit, but they are always noted on a new line.

My code solution was to take the cell, split it on line break, trim everything 5 characters after the last ':' and evaluate the the result against my regular expression. After that, some basic tally and text insert to adjacent cell.

The below is an example of how the function would be called.

'calling function from another source:

thecount = CountOfDateValues(Range("a1").Value) 'get count
Range("b1").Value = thecount 'put count to adjacent cell

Is there any code that will take the string value and return a count of matches to the regular expression?

Solution

You can also include newlines in the Pattern expression by using \n. this way, you don't have to split the text in an array:

Private Function String_CountRegExp_Debug()

    'Input of the test text
    Dim TestText As String
    TestText = "1/15/2013 1:30:11 AM Userx" & vbNewLine & _
            "Had to reboot system" & vbNewLine & _
            "1/15/2013 1:32:11 AM Userx" & vbNewLine & _
            "System running finished rebooting and appears to be working" & vbNewLine & _
            "11/15/2013 12:30:11 AM Userx" & vbNewLine & _
            "System hung again"

    'Input of the Pattern
    Dim RE_Pattern As String
    RE_Pattern = "(\d{1,2})\/(\d{1,2})\/(\d{4})\s(\d{1,2}):(\d{1,2}):(\d{1,2})\s([A,P]M).*\n"

    Debug.Print String_CountRegExp(TestText, RE_Pattern)

End Function

Public Function String_CountRegExp(Text As String, Pattern As String) As Long
'Count the number of Pattern matches in a string.

    'Set up regular expression object
    Dim RE As New RegExp
    RE.Pattern = Pattern
    RE.Global = True
    RE.IgnoreCase = True
    RE.MultiLine = True
    'Retrieve all matches
    Dim Matches As MatchCollection
    Set Matches = RE.Execute(Text)
    'Return the corrected count of matches
    String_CountRegExp = Matches.Count

End Function