ExcelInsider Team
When we work with large datasets, it is common to have important details like emails, IDs, or codes inside long text strings. We want to extract this information manually, which can be time consuming and error-prone. Excel’s built-in REGEXEXTRACT function and Custom VBA method allow us to automate this process using regular expressions to make data clean and become the analysis much faster
To extract data with Regex in Excel, follow these steps:
➤ Identify the column that contains your mixed text data.
➤ Use the built-in REGEXEXTRACT formula or create a VBA RegExpExtract function.
➤ Apply the formula/function to extract the desired text pattern.
In this article, we will explain how to use regex to extract data in Excel using the REGEXEXTRACT built-in function and a Custom VBA method.
How Does Regex Extraction Work in Excel?
Regex extraction in Excel means using regular expressions to find and pull out specific patterns of text from cells. Patterns could include email addresses, product codes, phone numbers, or dates. Excel’s REGEXEXTRACT function matches these patterns directly and extracts the data.
Applying REGEXEXTRACT Function to Extract Data with RegEx in Excel
The REGEXEXTRACT function in Excel is a built-in formula. It is best used when you have mixed text and need to pull out structured data like emails, phone numbers, order IDs, or codes. For example, when your dataset contains customer contact details, you can quickly extract only the email addresses without manual copying.
We have a customer database with a “Contact Info” column that contains both names and emails in mixture. We want to extract only the email addresses. We will use the REGEXEXTRACT function to do that.
Steps:
➤ Open your dataset. We have the Customer ID in Column A, and Contact Info with Email in Column B. We will store the Extract Values like( jhon.smith@gmail.com) in Column C.
➤ Click on the cell where you want the extracted result to appear (for example, C2 for the first result). Then press Enter.
=REGEXEXTRACT(B2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
In this formula,
- [A-Za-z0-9._%+-]+ → username part (letters, numbers, dots, underscores, etc.)
- @ → the at symbol
- [A-Za-z0-9.-]+ → domain name
- \.[A-Za-z]{2,} → domain extension like .com, .org, .net
You will see the extracted email address from cell B2 appear in C2.
➤ Drag the fill handle down to apply the formula to all rows in your dataset.
Note:
This built-in REGEXEXTRACT function is available in Excel for Microsoft 365 (Windows, Mac, and Web). If you have older versions use the next method.
Using Custom VBA to Extract Data with Regex in Excel
The Custom VBA method uses a macro written in Visual Basic for Applications (VBA) to search for and extract text that matches a regular expression pattern. This is helpful when your Excel version does not have built-in regex functions, or when you need custom regex capabilities. It works on all desktop Excel versions that support VBA macros.
We have a product description list where each entry contains a product name, a product code inside square brackets, and additional info. We will extract just the product code using the VBA.
Steps:
➤ Open your Excel workbook with the dataset. We have the Product ID in Column A and the Product Description in Column B. We will store the Extract Values like Product Code(LP-5521) in Column C.
➤Press Alt + F11 to open the VBA editor.
➤ In the VBA editor, go to Insert → Module.
➤ After clicking on Module A blank code window will open. Paste the following VBA code in the module window.
Function RegExpExtract(ByVal text As String, ByVal pattern As String) As String
Dim regex As Object
Dim matches As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = pattern
regex.Global = False
regex.IgnoreCase = True
If regex.Test(text) Then
Set matches = regex.Execute(text)
RegExpExtract = matches(0).Value
Else
RegExpExtract = ""
End If
End Function
➤ Click on File > Close and Return to Microsoft Excel. Or you can simply press Alt + Q to close the VBA window.
➤ Click in cell C2 and enter the formula. Then, press Enter.
=RegExpExtract(B2, "\[([A-Za-z0-9-]+)\]")
The Product Code (LP-5521) will now appear in E2
➤ Drag the fill handle down to apply the extraction to all rows.
Frequently Asked Questions
What is the regex extract formula in Excel?
The regex extract formula in Excel is =REGEXEXTRACT(text, pattern), where “text” is your cell reference and “pattern” is the regular expression.
How to extract data using regex?
You define a regex pattern that matches your target text, then use either REGEXEXTRACT or a VBA function to return the matched text.
How to use the extract function in Excel?
Type =REGEXEXTRACT(cell, “pattern”) in the result cell, replacing “cell” with your reference and “pattern” with your regex rule.
How can I extract data?
In Excel, you can extract data using regex, text functions (LEFT, MID, RIGHT), or filtering tools. Regex is best for complex, patterned data.
Concluding Words
The easiest methods are REGEXEXTRACT built-in function and the Custom VBA method, you can extract structured data quickly and accurately in Excel. If you have Excel 365 or an older desktop version, these regex methods allow you to handle complex text patterns efficiently with REGEXEXTRACT Function. And in any version you can use the VBA method.
