How to use RegEx in Excel to filter out email addresses from Cell - VBA Macro

Note: Microsoft has recently announced new REGEX functions in Excel. It is still a Beta version and is available only for some Microsoft insiders (Microsoft Community) . The functions will soon be available in Office 365.

Meanwhile, you can create your own Regular Expressions function using VBA. I'll show you how.

To use regex in VBA, you will have to add a reference of Regular Expression in your VBA editor. You can also create regex using CreateObject() method.

So lets create a Regular Expressions function.

Add RegEx Reference in VBA

To use regex in VBA, you will have to add a reference of Regular Expression in your VBA editor.

Open Excel and save the file in ".xlsm" format. Its a "macro enabled" Excel file.

• In the editor, select Tools from the top menu and choose References.

• Find Microsoft VBScript Regular Expressions 5.5 library, select it (or check it) and press OK.

➡️ Create regex in VBA using CreateObject() method

The Custom RegEx function (Macro)

In your VBA editor, open Module and copy the below code.

Option Explicit

Public Function RegExMatch(oRange As Range, sPattern As String) As Variant
    On Error GoTo e1

    Dim rE As RegExp
    Set rE = New RegExp

    Dim myMatches As MatchCollection
    Dim myMatch As Match

    With rE
        .IgnoreCase = True
        .Global = True
        .Pattern = sPattern    ' Assign the pattern.
    End With
    
    Dim rng As Variant
    Dim str As String
    
    For Each rng In oRange
        Set myMatches = rE.Execute(rng.Value)    ' Execute the string
        
        For Each myMatch In myMatches
            If Trim(str) = "" Then
                str = myMatch.Value
            Else
                str = str + ", " + myMatch.Value
            End If
        Next
    Next rng
    
    // return filtered values.
    RegExMatch = str
e1:
    Debug.Print Err.Description       '  show errors, if any.
End Function

Your own custom Regular Expression function is now ready. Since its a "public" function in a "Module", you can now access the function from your Excel worksheet, just like any other Excel function.

The function takes two parameters or arguments.

1) oRange - A range of data. The data that you provide can be multiple cells. It is required.

2) sPattern - A pattern or the "regular expression" to match. Its string value. Therefore, the pattern must be within double quotes. It is required.

Similar example: Extract pattern from string in Excel using VBA and RegEx

Extract email addresses from a cell

Let's put this function is use.

Although, its a User Defined Function or a UDF (also called a custom function), the RegExMatch() can be used just like any other Excel function.

For example, let us assume, I have few a list of names with email address in the first column. I want to filter out only the email ids from each cell and show it in another cell.

pattern to use for email address: \b[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}\b

Insert the function in the 2nd column like this.

=RegExMatch(A2, "\b[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}\b")

If you have multiple email addresses in a cell, it would extract and show all the email addresses in the 2nd column separated by a "comma".

Using a Range in RegExMatch() function

You can pass a range (of cells) to the function and it will return all the email addresses in a single cell.

Similar example: Check if worksheet cell has number using RegEx

← PreviousNext →