MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to Use RegEx in Microsoft Word


August 20, 2018 - by Suat M. Ozgur

How to Use RegEx in Microsoft Word

Lissa asks:

Is there a way to change a number (always a random number) after the word fox? Example: fox 23, bear 1, fox 398, frog 12, fox 15. I want to change the number to the same color of the word fox.

We can find and replace by format in Microsoft Word. This is a great feature to quickly find the formatted text, and even replace the entire text format in the document.

Select Advanced Find on the ribbon.

Find and Replace Dialog
Find and Replace Dialog

Enter the text to find, then click More button to see advanced options, and click on Format button.

Advanced Find Options
Advanced Find Options

Select Font option in the settings, then you can set up the text color that you would like to find in the document. Click OK to close Find Font dialog window.


Select text color in Find Font dialog.
Select text color in Find Font dialog.

Click Find Next, and you'll see the first occurance of the text being searched in certain color will be selected.

Find Next to find first occurance.
Find Next to find first occurance.

We can also make more complicated searches by using wildcards. However, Word's native search module doesn't let us make a search how Lissa asked.

That's where we can call RegEx into the game!

VBSCript Regular Expressions Library

VBA doesn't ship with any regular expression support. However Microsoft VBScript library contains powerful regular expression capabilities. This library is part of Internet Explorer 5.5 and later, so it is available on all computers running Windows XP, Vista, 7, 8, 8.1, or 10.

Mac Users

Since Internet Explorer is not a Mac application, this library doesn't exist in Mac. Therefore, VBA samples below don't work in Mac.

To use this library in VBA, switch to VBE, select Project and References in the VBE menu, then scroll down the list to find the item "Microsoft VBScript Regular Expressions 5.5", and tick it to include in the application.

VBScript Regular Expressions Library
VBScript Regular Expressions Library

Insert a new module, and copy and paste the following code into this module.

Sub doRegexFind()
Dim strSample As String
Dim objRegex As RegExp
Dim matches As MatchCollection
Dim fnd As Match

    strSample = "First product code is fox 12, second one is fox 22, and third product is fox 45."
    
    Set objRegex = New RegExp
    
    With objRegex
        .Pattern = "fox \d+"
        .Global = True
        .IgnoreCase = True
        Set matches = .Execute(strSample)
        For Each fnd In matches
            Debug.Print fnd
        Next fnd
    End With
End Sub

This procedure takes the sample text, finds the product codes by the given pattern - which is starting with "fox", single space and a number, and prints the matched codes in the Immediate window (hit Ctrl + G in VBE if it is not visible already).

Matched product codes printed in Immediate window.
Matched product codes printed in the Immediate window.

\d+ character class in the pattern defines one or more numeric characters, and pattern is basically "fox" prefix followed by a space followed by numbers.

More Info

Visit Regular Expression Language - Quick Reference for more information about character escapes, character classes, and anchors.

Copy and paste following code to see RegEx in action to remove the spaces from product codes.

Sub doRegexFindReplace()
Dim objRegex As RegExp
Dim matches As MatchCollection
Dim fnd As Match
Dim strSample As String

    strSample = "First product code is fox 12, second one is fox 22, and third product is fox 45."
    
    Set objRegex = New RegExp
    
    With objRegex
        .Pattern = "(fox) (\d+)"
        .Global = True
        .IgnoreCase = True
        strSample = .Replace(strSample, "$1$2")
    End With
    
    Debug.Print strSample
End Sub

This procedure replaces the sample text content by removing the spaces from the product codes matched with the given pattern, and prints the result text in the Immediate window.

Replaced text printed in the Immediate window.
Replaced text printed in the Immediate window.

Please note that pattern is slightly different than the first code. Terms in this pattern are enclosed with parentheses, and corresponding terms are used in the Replace method as $1 and $2 in order. This procedure simply joins the two terms without spaces.

Back to the Question

Let's go back to the sample text we used at the beginning of this article.

Sample Text
Sample Text

We need to find "fox" followed by numeric characters, and change the match by using the color of the "fox" section in the matched text.

Although RegEx is very good matching by the given pattern, it cannot replace the color of text in Word document. So we will combine RegEx and Word VBA methods in the following procedure.

Here are the steps:

  1. Find the matches with RegEx.
  2. Search each matched text by using Word Find method.
  3. Find the color of the first word in the found range.
  4. Change the color of the found range with the color in the previous step.

Switch to VBE, and insert a new module. Make sure VBScript Regular Expressions library is added to the project, and copy and paste the following code into this new module.

Sub doRegexMagic()
Dim str As String
Dim objRegex As RegExp
Dim matches As MatchCollection
Dim fnd As Match
    Set objRegex = New RegExp
    
    str = "fox"
    
    With Selection
        .HomeKey wdStory
        .WholeStory
    End With
    
    With objRegex
        .Pattern = str & " \d+"
        .Global = True
        .IgnoreCase = True
        Set matches = .Execute(Selection.Text)
    End With

    With Selection
        .HomeKey wdStory
        With .Find
            .ClearFormatting
            .Forward = True
            .Format = False
            .MatchCase = True
            For Each fnd In matches
                .Text = fnd
                .Execute
                With Selection
                    .Font.Fill.ForeColor = .Range.Words(1).Font.TextColor
                    .MoveRight wdCharacter
                End With
            Next fnd
        End With
        .HomeKey wdStory
    End With
End Sub

Run the code, and here is the result.

Result
Result

Download Word File

To download the Word file: how-to-use-regex-in-microsoft-word.docm

RegEx in Excel?

Regex is completely missing from Excel. However, we can still use VBScript Regular Expressions in Excel VBA.

Launch Excel, open a new workbook, and create the content as shown below.

Sample data in Excel
Sample data in Excel

Reference

This article has been inspired by Learn Excel 2010 - "Find & Replace Color of A Certain Word": Podcast #1714 published by Bill Jelen on May 21, 2013! So we wanted to use similar sample text as he used in the video. We just added numbers after the "fox".

Switch to VBE, and insert a new module. Make sure VBScript Regular Expressions library is added to the project just like you did in Word, and copy and paste the following code into this new module.

Sub doRegexMagicInExcel()
Dim str As String
Dim objRegex As RegExp
Dim matches As MatchCollection
Dim fnd As Match
Dim rng As Range
Dim cll As Range

    Set objRegex = New RegExp
    Set rng = Selection
    
    str = "fox"
        
    With objRegex
        .Pattern = "(" & str & ") (\d+)"
        .Global = True
        .IgnoreCase = True
        For Each cll In rng.Cells
            Set matches = .Execute(cll.Value)
            For Each fnd In matches
                cll.Value = .Replace(cll.Value, "$1$2")
            Next fnd
        Next cll
    End With
End Sub

Return to worksheet, and select the range with sample text. Run the macro, and see the result.

Result in Excel
Result in Excel

This procedure loops through the cells in the selected range, replaces the text in the cells by removing the spaces from the product codes matched with the given RegEx pattern.

Download Excel File

To download the Excel file: how-to-use-regex-in-microsoft-excel.xlsm

Title Photo: tito pixel / Unsplash


Suat M. Ozgur is the author / co-author of
Excel JavaScript UDFs Straight to the Point

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.