Regular Expressions in Excel or Access

If you do not want to restrict yourself to just like or equality in Access WHERE clauses, or you just want to be able to embed typical regular expressions in your Excel formulas, then try this short piece of VBA code. This code was my answer to one of the questions at StackOverflow.

Before you use the code you need to open a VBA project (press Alt+F11 to open the VBA editor), insert new module, then pick Tools -> References and add a reference to Microsoft VBScript Regular Expressions 5.5. Given that, paste the code below to the newly inserted module.

 

Function my_regexp(ByRef sIn As String, _ 
                   ByVal mypattern As String) _
                   As String
    Dim r As New RegExp
    Dim colMatches As MatchCollection
    With r
        .Pattern = mypattern
        .IgnoreCase = True
        .Global = False
        .MultiLine = False
        Set colMatches = .Execute(sIn)
    End With
    If colMatches.Count > 0 Then
        my_regexp = colMatches(0).Value
    Else
        my_regexp = ""
    End If
End Function

Now you may use the function above in your SQL queries. For example to extract the date in format YYYY-MM-DDthe first match, and will return an empty string if nothing has been matched.

Leave a Reply