VBA & Macro Problems

How to Use RegexReplace in Excel via VBA Custom Function

Rana Garcia Oct 10, 2026 871 views

Question details

The user wants to use a RegexReplace formula to manipulate text but finds it unavailable, despite having the VBScript Regular Expressions reference enabled.

How to Create and Use a RegexReplace Function in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to perform advanced text replacement using regular expressions directly within an Excel worksheet.
Observed behavior: The RegexReplace function is not recognized as a valid worksheet formula, and simply saving the workbook as XLSM does not add the functionality.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon to access the VBA Editor.

Solution 1Recommended

Create a Custom RegexReplace Function Using VBA

Since Excel does not natively feature a RegexReplace formula in older versions, you can build a User Defined Function (UDF) via VBA using the VBScript.RegExp object.

The Microsoft VBScript Regular Expressions 5.5 reference is a VBA library, not a collection of standard worksheet functions. Simply enabling the reference does not automatically generate formulas you can type into a cell.

By writing a short VBA script, you can expose this library to the front-end worksheet as a custom function.

Open the VBA Editor

Press Alt + F11 to launch the Visual Basic for Applications Editor in your Excel workbook.

Insert a New Module

In the VBA Editor menu, click on 'Insert' and select 'Module' to create a blank script window.

Write the Custom Function

Define a new function by typing 'Function RegexReplace(text As String, pattern As String, replacement As String) As String'. Inside the function, initialize the regex object using 'Set regex = CreateObject("VBScript.RegExp")'.

Configure RegExp Properties

Set 'regex.Pattern = pattern', 'regex.Global = True', and 'regex.IgnoreCase = True' depending on your matching needs. Finally, assign the output with 'RegexReplace = regex.Replace(text, replacement)'.

Use the Formula in Your Worksheet

Close the VBA editor. You can now select any cell and type '=RegexReplace(A1, "[0-9]", "")' to strip numbers from the text in cell A1.

Create a Custom RegexReplace Function Using VBA

Security Warning: Avoid sending confidential workbook data to untrusted online or third-party web services for regex formula processing. Always perform data sanitization locally using VBA.

Solution 2

Update to an Excel Version with Native Regular Expressions

Microsoft is gradually rolling out native regex support to newer Excel versions, eliminating the need for VBA.

Advanced Data Processing in WPS Office

Create Custom Regex Functions Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to easily build and execute custom formulas like RegexReplace without compatibility issues.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document where you need to perform regular expression replacements.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic' to open the VBA Editor.
3. Insert the Macro: Go to Insert > Module, and paste your VBScript.RegExp custom function code into the editing window.
4. Save and Execute: Save your file as a Macro-Enabled Workbook (.xlsm) and use your newly created =RegexReplace() function directly in the spreadsheet cells.

Highly compatible with Microsoft Excel .xlsm and .xlsx formatsRobust built-in VBA editor for custom macros and user-defined functionsLightweight application providing superior performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Does saving a file as XLSM automatically enable regex functions?

No. Saving a file as an XLSM simply changes the file format to allow the storage of macros. You still need to manually write the VBA code to define the RegexReplace custom function before you can use it.

Why do I get a #NAME? error when typing RegexReplace?

This error occurs because Excel does not recognize RegexReplace as a built-in function. You must define it as a User Defined Function (UDF) using the VBA Editor before the worksheet can calculate it.

Is there a way to use regex without VBA?

If you do not want to use VBA, you will need to update to a newer Microsoft 365 version that supports native REGEXREPLACE formulas, or use complex combinations of standard text functions (like SUBSTITUTE, FIND, and MID) to mimic basic regex behavior.

Excel & Data Problems