Extract text within parentheses in Excel using RegEx and VBA

In this article, I am going to show you how to use RegEx (regular expressions) in Excel to extract text within parentheses using a custom regular expression function in VBA.

A text or a string within parentheses may look like this.

Arun (Programmer), 42

Now, I want to extract the value or the text within the round brackets (parentheses).

To do this, I am going to use a custom regular expression function. Its a piece of code (a macro) that I wrote to use RegEx in Excel.

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.

So, let's create the RegEx 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.

➡️ You can also create regex using CreateObject() method.

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.

The Custom RegEx function (Macro)

Open VBA editor and create a "Module". Copy the below code in the Module.

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 the result.
    RegExMatch = str
e1:
    Debug.Print Err.Description
End Function

You are now ready to use the RegExMatch() function in your Excel worksheet. Since its a Public function, it can be accessed from a worksheet like other Excel functions.

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.

Extract text within parentheses in Excel

You can use the "RegExMatch()" function like a formula. For example,

=RegExMatch(A2, "\(([^)]+)\)")

and, drag it down to other rows.

extract text within parentheses in excel using regex

The first argument is the column (it can a text). The second argument is the "RegEx" (or the regular expression) to filter out the texts within the brackets.

As you can see (in the above image), we have extracted the text within the round brackets. However, the texts are still within the parentheses. Let's remove the "parentheses".

Remove Parentheses from Text

There's a simple formula to remove parentheses from a text. You can SUBSTITUTE() function to remove the parentheses.

=SUBSTITUTE(SUBSTITUTE(RegExMatch(A2, "\(([^)]+)\)"),"(",""),")","")

using regex in excel to extract texts within parentheses

Extract text from Parentheses within a Range

You can use the RegExMatch() function to extract texts from Parentheses within a Range. For example,

=RegExMatch(A2:A4, "\(([^)]+)\)")

The result will be displayed in a single column.

← PreviousNext →