Regular Expressions in Excel

February 22, 2008

Regular Expressions are very powerful pattern matching tools that can be used to find, return, or even substitute string patterns.

To use these, you’ll need to enable the VB Reg-ex Library:

• In Visual Basic Editor - Tools|References
• Select “Microsoft VBScript Regular Expressions 5.5″

For Help with Regular Expressions, Google is your friend, but you might start here:

http://www.regular-expressions.info/reference.html

Here are a couple I have found helpful:

Public Function RegMatch(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Boolean
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘This function returns whether or not the Source string contains the pattern
‘ Modified from http://www.dailydoseofexcel.com/archives/2005/08/13/pattern-matching/

Dim reg As New RegExp
reg.IgnoreCase = IgnoreCase
reg.MultiLine = MultiLine
reg.Pattern = Pattern
RegMatch = reg.Test(Source)
End Function

This one returns the part(s) of the Source string that contain the pattern. Could also be modded for replacement, but I think the link contains examples that already do that.

Function RegExtract(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True, Optional Glbal As Boolean = True) As String
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Modified from http://www.vbaexpress.com/kb/getarticle.php?kb_id=68
‘ For help with regular expressions: http://www.regular-expressions.info/reference.html

‘Dimension the RegExp objects
Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, OutPutStr As String

‘ create the RegExp Object with late binding
Set RegEx = CreateObject(”vbscript.regexp”)

‘ set the RegExp parameters
With RegEx
.IgnoreCase = IgnoreCase
.MultiLine = MultiLine
.Global = Glbal ‘Not sure what this does, don’t care right now
.Pattern = Pattern
End With

OutPutStr = “”
Set RegMatchCollection = RegEx.Execute(Source)
‘Loop through each match in the string and concatenate them
For Each RegMatch In RegMatchCollection
OutPutStr = OutPutStr & RegMatch
Next
‘Put the extracted match into the corresponding B value cell
RegExtract = OutPutStr

End Function

Posted in Excel