Excel VBA Regex: Use Patterns to Find and Replace Text

Yes. In Excel VBA for Windows, you can use regular expressions by creating a VBScript.RegExp object, then calling its methods to test, find, or replace text. VBA does not have a built-in Regex type, and the Windows scripting engine is different from the regex engine used by Excel’s newer worksheet functions.

What regex means in Excel VBA

A regular expression, often shortened to regex or regexp, is a text pattern. Instead of searching only for one exact string, a pattern can describe a class of strings: for example, “INV-” followed by five digits, or a sequence of two or more spaces. In VBA, a regex can help validate entries, extract information from text, or standardize repeated patterns across cells.

The usual VBA approach on Windows is to automate the Microsoft VBScript regular-expression engine. The object exposes properties such as Pattern and IgnoreCase, and methods including Test, Execute, and Replace. You can create it with late binding, so you do not have to add a reference in the Visual Basic Editor. That convenience does not install the engine; if the component is unavailable or restricted on a particular computer, object creation can fail.

Regex is useful when a text rule is awkward to express with ordinary string functions. It is not automatically the best choice for every cleanup task. For a fixed prefix, a simple Left check may be easier to read and maintain than a pattern.

Run a simple regex from a VBA macro

This example checks the text in cell A2 for an invoice code made from “INV-” and exactly five digits. It displays the first match and leaves the worksheet unchanged. Change the worksheet name, cell, or pattern to suit your workbook. The sample assumes A2 contains ordinary text rather than an Excel error value.

Sub FindInvoiceCode()
    ' Save a copy of the workbook before running any macro.
    Dim ws As Worksheet
    Dim re As Object
    Dim matches As Object
    Dim sourceText As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    sourceText = CStr(ws.Range("A2").Value2)

    ' Late binding avoids a required reference in the VBA project.
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Pattern = "INV-[0-9]{5}"
        .Global = False
        .IgnoreCase = True
        .MultiLine = False
    End With

    Set matches = re.Execute(sourceText)
    If matches.Count > 0 Then
        MsgBox matches(0).Value
    Else
        MsgBox "No invoice code found."
    End If
End Sub

To add the macro, open the workbook in desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. On a Mac, the Visual Basic Editor is also available, but the Windows VBScript.RegExp COM object is not; this example therefore is not a cross-platform solution. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code.

Macros cannot be undone with Ctrl+Z. The example only reads a cell, but make a separate copy before adapting it to write, delete, or replace worksheet values. On Windows, Alt+F11 is the shortcut to the VBA editor; on Mac, the editor shortcut can vary with keyboard settings and Excel version, so use the Developer tab or Excel’s Tools > Macro > Visual Basic Editor command if the shortcut differs.

Set the pattern and choose the right method

The Pattern property contains the expression to match. In the example, [0-9] means one digit, and {5} means exactly five repetitions. The pattern does not require the entire cell to contain only an invoice code: it finds a matching substring wherever it appears. To require a full-cell match, use start and end anchors, such as ^INV-[0-9]{5}$. An anchor checks a position rather than matching a character.

Test(text) returns True or False when you only need to know whether a match exists.
Execute(text) returns a collection of matches. Each match has a Value property, and the collection can contain captured submatches as well.
Replace(text, replacement) returns a new string with matching text replaced. It does not change a worksheet cell unless your code writes the returned string back to the sheet.

Three properties commonly control behavior. Set Global to True to find or replace every match rather than only the first one. Set IgnoreCase to True when uppercase and lowercase letters should be treated alike. MultiLine affects how the line anchors ^ and $ behave in text containing line breaks; it does not make the expression process worksheet rows separately. For a single cell with a simple value, the default of False is often appropriate.

Pattern examples and practical uses

Regex patterns are easiest to maintain when they state a precise rule and are accompanied by examples of valid and invalid text. These patterns illustrate common building blocks for the VBScript engine:

^[0-9]{4}$ matches a string containing exactly four digits.
^[A-Z]{2}-[0-9]{6}$ matches two uppercase letters, a hyphen, and six digits, with no extra characters.
[ ]{2,} finds a run of at least two ordinary spaces. It does not include tabs or other whitespace.
cat|dog matches either alternative within the searched text. Add boundaries or anchors if the words must not be part of a longer string.

These examples are patterns, not complete macros. A pattern that accepts the right shape does not necessarily prove that a value is meaningful. For instance, a pattern can check that a date is written with digits and separators, but separate date logic is needed to determine whether the day exists in that month. Similarly, matching an email-like string is not proof that an address exists or can receive mail.

For worksheet-scale tasks, a macro can loop through a range, apply Test to each cell, and write results to a separate column. Keep the source values intact until you have reviewed the output. For large ranges, read the range into a VBA array, process the array, then write results back in a controlled step; repeatedly reading and writing individual cells can be slower. The regex itself does not make a macro faster by default, so test it on a copy and a representative sample.

VBScript regex syntax and its limits

The engine used by VBScript.RegExp is an older regular-expression implementation, not the same thing as .NET regex, PCRE, JavaScript regex, or every online regex tester. Common constructs such as character classes, grouping, alternation, quantifiers, and anchors are useful, but advanced syntax may not transfer. In particular, lookbehind and many newer regex features are not supported. A pattern copied from a modern regex tool can fail or behave differently.

Test patterns against both examples that should match and examples that should not. For instance, if a code must have exactly five digits, test a four-digit value, a six-digit value, and a value with letters among the digits. Also confirm whether punctuation around the code is allowed. An unanchored pattern can find a valid-looking substring inside a longer, invalid string, which may be intended for extraction but wrong for validation.

The VBScript engine also is not a complete substitute for language-aware validation. Character classes and shorthand rules can differ across engines, especially for Unicode text. If a workbook contains names or identifiers from multiple writing systems, verify the behavior with representative data rather than assuming a pattern designed around English letters will cover every character.

VBA regex versus Excel worksheet regex functions

VBA code and worksheet formulas solve related but different problems. In supported, up-to-date Microsoft 365 builds, Excel also provides worksheet functions such as REGEXTEST, REGEXEXTRACT, and REGEXREPLACE. These are formulas entered in cells; they are not VBA methods, and their regex engine and availability are not identical to VBScript.RegExp. Availability can depend on the Excel platform and update channel, so check the function’s support information in the version you use.

Choose worksheet functions when the result should update as cell values change and the function is available to workbook users. Choose VBA when the task involves a procedure, such as processing a selected range, coordinating several steps, or running a controlled import. If a workbook must work in older Excel releases or across Windows and Mac, avoid assuming either a new worksheet function or a Windows-only COM object is present. Test the actual target environments.

For simpler jobs, built-in VBA string functions such as InStr, Left, Mid, and Replace may be more portable and transparent. Power Query can also transform and clean imported text through its interface. These alternatives are often easier for colleagues to maintain when the rule is straightforward or the process is primarily data preparation rather than macro automation.

Windows, Mac, and Excel for the web

The VBScript.RegExp approach is intended for desktop Excel on Windows. Late binding removes the need to tick a reference in Tools > References, but it does not make a Windows COM component available on Mac. A Mac workbook that calls this object can fail when the macro runs. If a workbook must be shared between platforms, use a solution supported on both platforms, such as carefully chosen native VBA string operations, a verified cross-platform VBA library, or an available worksheet function.

Excel for the web does not run VBA macros. A workbook may be stored and viewed there, but the VBA procedure must be run in desktop Excel. Office Scripts are a separate automation option for supported web workflows; they use TypeScript rather than VBA and do not use the VBScript.RegExp object. Choosing among these options depends on where the workbook will be opened and who needs to run the process.

Common errors and safe macro practices

If CreateObject("VBScript.RegExp") raises an error, first check that the macro is running in desktop Excel on Windows and that system policy or the computer’s configuration has not disabled the component. Late binding avoids a missing-reference compile error, but it cannot overcome a component that is unavailable. Early binding is another Windows option: in the VBA editor, open Tools > References and, if listed, select Microsoft VBScript Regular Expressions 5.5. This gives access to its types and constants, but a project that depends on that reference can be less convenient to move between computers.

Other frequent problems include a pattern that is too broad, forgetting to set Global when every occurrence is needed, and writing a replacement result into the wrong range. A pattern that works in a browser-based regex tester may use syntax the VBScript engine does not recognize. Diagnose these issues with a small set of sample strings before applying the pattern to an entire workbook.

Keep a backup copy before running a macro that changes cells; Ctrl+Z cannot reverse VBA changes.
Use a test range or write proposed results to a new column before replacing source data.
Do not enable all macros indiscriminately. Run only workbooks and code you trust, following your organization’s security rules.
Save macro-enabled work as .xlsm; saving as a macro-free format can remove VBA code.

Regex itself does not grant a macro extra permissions, but macros can change workbook data and may perform other actions. The right safeguard is to review the code, limit what it changes, and keep a recoverable copy—not to treat a successful match as proof that a workbook is safe.

Frequently Asked Questions

Does Excel VBA have a built-in Regex function?
No. VBA does not provide a native Regex class. On Windows, the common option is the VBScript.RegExp object, which you can create with late binding or use through a reference where available.

Can I use VBScript.RegExp on a Mac?
Not as a Windows COM object. Mac Excel can run VBA, but this particular object-based approach is not cross-platform. Use a tested alternative that supports Mac or choose a suitable worksheet-based method.

How do I find every match instead of the first one?
Set the regex object’s Global property to True before calling Execute or Replace. With Execute, inspect the returned match collection rather than reading only its first item.

Why does my regex pattern work online but fail in VBA?
Regex engines have different syntax and feature sets. VBScript.RegExp does not support some newer constructs, including lookbehind. Check the pattern against VBScript-compatible syntax and test it with sample inputs in the target Excel environment.

Can VBA regex validate an email address or date completely?
A pattern can enforce a chosen text format, but format matching alone cannot confirm that an email address is deliverable or that a date is a real calendar date. Use additional checks appropriate to the data’s purpose.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.