Use Regular Expression by VBA

use_regex.vba

Option Explicit

Public Function RegExpTest(text As String, pattern As String, Optional match_case As Boolean = True) As Boolean
  Dim regex As Object

  On Error GoTo ErrHandl

  Set regex = CreateObject("VBScript.RegExp")
  regex.pattern = pattern
  regex.Global = True
  regex.MultiLine = True

  If True = match_case Then
    regex.ignorecase = False
  Else
    regex.ignorecase = True
  End If

  RegExpTest = regex.Test(text)

  Exit Function

ErrHandl:
    RegExpTest = CVErr(xlErrValue)
End Function

Public Function RegExpReplace(text As String, pattern As String, text_replace As String, Optional match_case As Boolean = True) As String
  Dim regex As Object

  On Error GoTo ErrHandle
  Set regex = CreateObject("VBScript.RegExp")

  regex.pattern = pattern
  regex.Global = True
  regex.MultiLine = True

  If True = match_case Then
    regex.ignorecase = False
  Else
    regex.ignorecase = True
  End If

  RegExpReplace = regex.Replace(text, text_replace)

  Exit Function

ErrHandle:
  RegExpReplace = CVErr(xlErrValue)
End Function