How to use regular expressions in Excel

Regex-Excel

' Using Regex Functions in Excel

'http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/

' Turn on VBScript Regular Expressions 5.5 reference thing


Function M(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        M = "Matches '" & Pattern & "'"
    Else
        M = ""
    End If
End Function

Function StartsWith(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = "^" & Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        StartsWith = "Starts with '" & Pattern & "'"
    Else
        StartsWith = ""
    End If
End Function

Function EndsWith(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern & "$"
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        EndsWith = "Ends with '" & Pattern & "'"
    Else
        EndsWith = ""
    End If
End Function

' =S(#REF!,"(.{4})(.{2})(.{2})","$2-$3-$1")
' ctrl + G - for each cl in Selection:cl.Formula=cl.Formula:Next cl
Function S(Value As String, Pattern As String, ReplaceWith As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    r.Global = True
    S = r.Replace(Value, ReplaceWith)
End Function