How to Match a Pattern with RegEx in Excel (2 Examples)

Working with large datasets in Excel helps identify the values that follow a specific pattern. You can use Regular Expressions (RegEx) in Excel, which helps you to find and match patterns such as email addresses, phone numbers, or specific text formats quickly. This is useful for data validation, cleaning, and advanced filtering without manual searching.

What Is RegEx in Excel?

RegEx (short for Regular Expression) is a sequence of characters that defines a search pattern. It is widely used in programming, text processing, and data validation to find strings that match a specific format. For example, the pattern ^\d{4}$ matches any string with exactly four digits.

The REGEXTEST function is a built-in Excel tool that checks if a piece of text matches a specific Regular Expression (Regex) pattern and returns TRUE or FALSE.
We can use it when we need to validate formats such as email addresses, phone numbers, IDs or check if a string contains a specific structure.

We have a dataset table that contains various customer emails. Using REGEXTEST, we check if the email ends with @mycompany.com. The result is a TRUE for matches and FALSE for non-matches and make it easy to filter valid company addresses.

Steps:

➤ Open your dataset. We have an ID in Column A, Name is in Column B and Email address in Column C. We have a list of customer email addresses, and we want to identify which ones are valid company emails like @mycompany.com. We will use the REGEXTEST function to match the pattern. We will store the data match pattern in Column D.

➤ You should Identify your matching pattern. In this case we want to match only emails ending with @mycompany.com. You can see it on your excel formula bar that references the cell C2.

The Regex pattern for this is @mycompany\.com$. Here,

• @mycompany → literal text
• \. → matches the dot (.) character
• com → literal text
• $ → ensures it appears at the end of the text

➤ Click in cell D2 (next to the first email) and enter:

Which references to the formula bar to cell D2. You will see return TRUE if the email in C2 ends with @mycompany.com, otherwise FALSE.

➤ Drag the fill handle down to fill the formula for all rows in your dataset. You can now filter the table to only show TRUE values or sort them so valid company emails appear at the top.

RegExpMatch is a custom User Defined Function (UDF) created in VBA that helps Excel to test text against a Regular Expression and return TRUE or FALSE.
You can use it when your Excel version does not have native REGEX functions or when you want more control over case sensitivity and pattern handling. It is best for datasets such as product codes, employee IDs, or formatted numbers where validation is needed.

We have a dataset that contains SKUs in different formats. Using a Custom VBA RegExpMatch function, we check if they match the exact format AA-9999. The output column quickly flags which SKUs meet the standard.

Steps:

➤ Open your dataset. We have a Product ID in Column A and Product Name is in Column B and SKU-Code in Column C. We want to manage a product catalog that contains SKU codes in various formats. We will use only SKUs in the format AB-1234 (two uppercase letters, a dash, and four digits). We will use a Custom VBA RegExpMatch UDF to identify valid SKUs.

➤ Open the Excel workbook, press  Alt  +  F11  to open the VBA Editor window. Go to Insert > Module.

➤ A blank code window will open. Paste the following VBA code in the module window.

Function RegExpMatch(ByVal Text As String, ByVal Pattern As String, Optional ByVal MatchCase As Boolean = True) As Boolean
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
RE.Pattern = Pattern
RE.Global = False
RE.IgnoreCase = Not MatchCase
RegExpMatch = RE.Test(Text)
End Function

How it works:

Pattern is your regex expression (e.g., “^[A-Z]{2}-\d{4}$” for two letters, dash, and four digits).
MatchCase controls case sensitivity (default is True).

➤ Click on File to Close and Return to Microsoft Excel to save your workbook as Macro-Enabled Workbook (.xlsm). Then press  Ctrl  +  S  and close the VBA window.

➤ click in cell D2 and enter the following formula. Excel sheet with formula in D2 returning TRUE/FALSE

➤Drag the fill handle down Column D to check all rows in your dataset.

Frequently Asked Questions (FAQs)

How to use regexmatch?

You can use RegEx in Excel via VBA by enabling the VBScript RegExp library and writing a function to return matches.

How to do a regex match in Excel?

Set up a VBA macro with RegExp.Test to check if a cell’s text matches the desired pattern.

Does Excel have a regexmatch?

No, Excel doesn’t have a built-in REGEXMATCH function like Google Sheets, but you can simulate it with VBA.

What is the regex function?

It’s a function that uses a regular expression pattern to check, search, or replace text based on defined rules.

Concluding Words

Matching patterns in Excel becomes powerful with the VBA RegExp method. While Excel lacks a native REGEXMATCH function, VBA enables flexible and precise pattern matching that can handle everything from validating formats to extracting complex data.