Multiple Apps

UDF wrappers for RegExp Find and Replace methods

Ease of Use


Version tested with

2000, 2002 

Submitted by:



Regular Expressions offers extremely powerful text parsing capabilities. This entry provides user-defined function "wrappers" for the RegExp.Find and RegExp.Replace methods, enabling quick "plug and play" for Regular Expressions in your VB/VBA projects 


This entry builds on the excellent work by VBAX member brettdj in: "Using Regular Expressions (RegExp) in Excel" These functions, RegExpFind and RegExpReplace, are very useful for text-parsing tasks where you can define a rule for the text you are seeking, but those rules are not easily supported by the usual text-handling functions such as Left, Right, Mid, InStr, InStrRev, and Replace. This entry shows how to encapsulate RegExp.Find and RegExp.Replace into UDFs that can be implemented very quickly to support either building worksheet formulas in Excel, or to support code in a VB/VBA project. RegExpFind returns either an array with all matching substrings, a specific substring based on position number, or the last matching substring, depending on the value of the Pos argument; if no match is found, the function always returns an empty string, regardless of the setting for the Pos argument. RegExpReplace returns a string where all matching substrings in the LookIn string are replaced or just the first matching substring is replaced, depending on the value of the ReplaceAll argument. (If there are no matches, RegExpReplace simply returns the original LookIn string.) This entry is rated as 'Hard' for 'Ease of Use' only because mastering RegExp pattern strings can be difficult at first; adding the code itself to your project is not difficult. Several primers are available online, such as at: or by searches at or 


instructions for use


Option Explicit Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _ Optional MatchCase As Boolean = True) ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a ' pattern (PatternStr). Use Pos to indicate which match you want: ' Pos omitted : function returns a zero-based array of all matches ' Pos = 0 : the last match ' Pos = 1 : the first match ' Pos = 2 : the second match ' Pos = <positive integer> : the Nth match ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function ' returns an empty string. If no match is found, the function returns an empty string ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]). ' If you use this function in Excel, you can use range references for any of the arguments. ' If you use this in Excel and return the full array, make sure to set up the formula as an ' array formula. If you need the array formula to go down a column, use TRANSPOSE() Dim RegX As Object Dim TheMatches As Object Dim Answer() As String Dim Counter As Long ' Evaluate Pos. If it is there, it must be numeric and converted to Long If Not IsMissing(Pos) Then If Not IsNumeric(Pos) Then RegExpFind = "" Exit Function Else Pos = CLng(Pos) End If End If ' Create instance of RegExp object Set RegX = CreateObject("VBScript.RegExp") With RegX .Pattern = PatternStr .Global = True .IgnoreCase = Not MatchCase End With ' Test to see if there are any matches If RegX.test(LookIn) Then ' Run RegExp to get the matches, which are returned as a zero-based collection Set TheMatches = RegX.Execute(LookIn) ' If Pos is missing, user wants array of all matches. Build it and assign it as the ' function's return value If IsMissing(Pos) Then ReDim Answer(0 To TheMatches.Count - 1) As String For Counter = 0 To UBound(Answer) Answer(Counter) = TheMatches(Counter) Next RegExpFind = Answer ' User wanted the Nth match (or last match, if Pos = 0). Get the Nth value, if possible Else Select Case Pos Case 0 ' Last match RegExpFind = TheMatches(TheMatches.Count - 1) Case 1 To TheMatches.Count ' Nth match RegExpFind = TheMatches(Pos - 1) Case Else ' Invalid item number RegExpFind = "" End Select End If ' If there are no matches, return empty string Else RegExpFind = "" End If ' Release object variables Set RegX = Nothing Set TheMatches = Nothing End Function Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _ Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True) ' This function uses Regular Expressions to parse a string, and replace parts of the string ' matching the specified pattern with another string. The optional argument ReplaceAll controls ' whether all instances of the matched string are replaced (True) or just the first instance (False) ' By default, RegExp is case-sensitive in pattern-matching. To keep this, omit MatchCase or ' set it to True ' If you use this function from Excel, you may substitute range references for all the arguments Dim RegX As Object Set RegX = CreateObject("VBScript.RegExp") With RegX .Pattern = PatternStr .Global = ReplaceAll .IgnoreCase = Not MatchCase End With RegExpReplace = RegX.Replace(LookIn, ReplaceWith) Set RegX = Nothing End Function

How to use:

  1. Add the code above to a regular module in your VB/VBA project
  2. Use the functions in worksheet cells (if you are using Excel--see attached example file), or in the code of your VB/VBA project

Test the code:

  1. Download the attached example file
  2. Change the various inputs to the RegExpFind and RegExpReplace examples, and test the end result

Sample File: 11.97KB 

Approved by mdmackillop

This entry has been viewed 230 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express