Regular Expressions In Excel 2007

• Tweet
• 

        <b>'installing':</b>
Open up excel 2007, hit alt+f11, press insert->module, paste the below

<b>Usage:</b>
=ReFind( targetString as Cell/String, RegExp as String, optional IgnoreCase as Boolean )
RETURNS match as String

=ReReplace( targetString as Cell/String, RegExp as String, replaceString as String, optional IgnoreCase as Boolean )
RETURNS replacedString as String

<b>Sample:</b>
=ReFind("123 fake st","[0-9]*")
RETURNS "123"

=ReReplace("123 fake st","FAKE","real",TRUE)
RETURNS "123 real st"

<b>Code</b>

Option Explicit

Function ReReplace(ReplaceIn, _
        ReplaceWhat As String, ReplaceWith As String, Optional IgnoreCase As Boolean = False)
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = IgnoreCase
    RE.Pattern = ReplaceWhat
    RE.Global = True
    ReReplace = RE.Replace(ReplaceIn, ReplaceWith)
End Function
Function ReFind(FindIn, FindWhat As String, _
        Optional IgnoreCase As Boolean = False)
    Dim i As Long
    Dim matchCount As Integer
    Dim RE As Object, allMatches As Object, aMatch As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)
    matchCount = allMatches.Count
    If matchCount >= 1 Then
        ReDim rslt(0 To allMatches.Count - 1)
        For i = 0 To allMatches.Count - 1
            rslt(i) = allMatches(i).Value
        Next i
        ReFind = rslt
    Else
        ReFind = ""
    End If
End Function

<b>Edit:</b> Updated 26 Nov to prevent errors in the case of no match in ReFind and cleaned up the code    

Tags:
• Excel
• regex

Comments

Snippets Manager replied on Wed, 2011/09/14 - 10:18am

That was awesome! Thanks for your help! I knew there had to be a simple way of extracting the numeric portion in the middle of a string. I changed the * to a + (for some reason I was having some issues with *) and used "\d" instead of "[0-9]": =refind(K4,"\d+")

• Login or register to post comments