[VBA] Automating Data Replacement with Regular Expressions (RegExp) and Practical Examples

Overview

In Excel VBA, using Regular Expressions (RegExp) allows you to efficiently perform advanced text searching and replacement.

This article introduces how to delete or replace characters that match specific patterns, along with a list of useful regular expression patterns.

Sample Code

Sub ReplaceWithRegExp()
    Dim regObj As Object
    Dim targetCell As Range
    
    ' Create the Regular Expression object
    Set regObj = CreateObject("VBScript.RegExp")
    regObj.Global = True
    regObj.Pattern = "\D" ' Target non-numeric characters
    
    ' Convert data in the target range
    For Each targetCell In Range("B2:B6")
        targetCell.Offset(0, 1).Value = _
            regObj.Replace(StrConv(targetCell.Value, vbNarrow), "")
    Next
End Sub

Code Explanation

• CreateObject(“VBScript.RegExp”)To use regular expression features in VBA, we create a VBScript RegExp object.
• Global PropertyBy setting this to True, the code targets all matches within the string (not just the first one).
• Pattern PropertyThis sets the rule for the search. For example, \D means “any character that is NOT a number.”
• Replace MethodReplaces the text matching the pattern with a specified string. In this example, we replace matches with an empty string (“”) to leave only the numbers.
• StrConv FunctionThis function handles character width conversion. In the example, vbNarrow is used to convert full-width characters to half-width before processing.

List of Common Regular Expression Patterns

Practical Applications

• Removing hyphens from phone numbers or postal codes.
• Bulk removal of symbols other than alphanumeric characters.
• Extracting sentences that contain specific keywords.
• Formatting dates into a unified style.

Summary

By utilizing Regular Expressions, you can write concise code for complex text processing.

Combining VBA with RegExp is particularly effective for tasks involving bulk data conversion. By customizing the search patterns, you can apply this to data cleansing, format standardization, and various other scenarios.