Excel RegEx Extraction

Question

recently I've been trying to extract some strings from text in excel. I used script from other post here: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Since Macro code is working fine I couldn't use in Cell function, it's showing #NAME? error. I've included "Microsoft VBScript Regular Expressions 5.5" but still no luck.

I can use it with macro but script needs some changes. I would like to have some strings in A1:A50, then to B1:B50 extract date in format DD Month YYYY (e.g. 28 July 2014) and to C1:C50 extract account no in format G1234567Y.

For now script is replacing date with "". Regular Expression for date is correct but how to insert date into B column? And then A/c no to C column working on 1:50 range? This is the code:

Sub simpleRegex()
    Dim strPattern As String: strPattern = "[0-9][0-9].*[0-9][0-9][0-9][0-9]"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range
    Dim Out As Range

    Set Myrange = ActiveSheet.Range("A1")
    Set Out = ActiveSheet.Range("B1")
    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

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

        If regEx.Test(strInput) Then
            Out = regEx.Replace(strInput, strReplace)
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

Thank You kindly for any assistance.

Answer 1

Currently your replacing the matching string with an empty string "" so that's why your getting no result. You need to return the actual match using () to indicate match set and $1 to retrieve it.

---

Based on your example, I'll assume your text in column A looks like this: 28 July 2014 G1234567Y

Here is a routine that will split apart the text into a date and then the text following the date.

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("A1:A50")

    For Each C In Myrange
        strPattern = "([0-9]{1,2}.*[0-9]{4}) (.*)"
        'strPattern = "(\d{1,2}.*\d{4}) (.*)"

        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, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Result:

---

To use an in-cell function, set it up to extract a single piece such as Date or everything else. The following code will extract the date. Cell B1 would have the following equation: =extractDate(A1)

Function extractDate(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strRaplace As String
    Dim strOutput As String

    strPattern = "(\d{1,2}.*\d{4}) (.*)"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

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

        If regEx.Test(strInput) Then
            extractDate = regEx.Replace(strInput, "$1")
        Else
            extractDate = "Not matched"
        End If
    End If
End Function

To make another function for extracting the rest of the date simply change $1 to $2 and it will return the second defined match in the pattern.