Master Excel’s REGEXEXTRACT for Data Extraction

If you’ve ever struggled with extracting specific patterns from text in Excel, then REGEXEXTRACT is the function you need to know! While regular expressions (regex) are widely used in programming and data analysis, Google Sheets introduced REGEXEXTRACT to make pattern-based text extraction easier. Unfortunately, Excel does not have a built-in REGEXEXTRACT function, but with a few tricks—such as Power Query or VBA—you can achieve the same powerful results.

What is REGEXEXTRACT?

The REGEXEXTRACT function allows you to extract text that matches a specific pattern. It’s perfect for pulling out emails, phone numbers, dates, or other structured information from messy data.

Syntax (Google Sheets Only):

=REGEXEXTRACT(text, pattern)

• text – The cell containing the data to extract from.
• pattern – The regular expression (regex) that defines the structure of the text you want to extract.

Example 1: Extracting an Email Address

If A1 contains:
👉 John Doe john.doe@example.com

You can extract the email with:

=REGEXEXTRACT(A1, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")

💡 Result: john.doe@example.com

How to Use REGEXEXTRACT in Excel (Alternatives)

Since Excel does not have REGEXEXTRACT natively, you’ll need Power Query or VBA to achieve the same functionality.

Method 1: Using Power Query for Regex Extraction

Power Query is a powerful data transformation tool in Excel that supports regex via M code.

Steps to Extract Text Using Power Query:

1. Select your data and open Power Query (Data → Get & Transform → From Table/Range).
2. Click “Add Column” → “Custom Column”.
3. Use the following M code to extract an email:

Text.Select([Column1], {"A".."Z", "a".."z", "0".."9", "@", ".", "_", "-"})

4. Click Close & Load to return the extracted text to Excel.

Method 2: Using VBA for REGEXEXTRACT in Excel

For a more dynamic solution, you can use VBA to replicate REGEXEXTRACT.

Steps to Set Up VBA Regex Extraction:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert → Module.
3. Copy and paste the following VBA function:

Function RegExExtract(ByVal txt As String, ByVal pattern As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = pattern
    regex.Global = False
    If regex.Test(txt) Then
        RegExExtract = regex.Execute(txt)(0)
    Else
        RegExExtract = ""
    End If
End Function

4. Save and exit the VBA editor.

5. Use the function in Excel like this:

=RegExExtract(A1, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")

💡 Result: john.doe@example.com

Get the cheapest Office keys today and enjoy instant activation, genuine licenses, and unbeatable prices!