Pattern Matching in Word VBA Find

Question

I am trying to help someone automate some very simple text editing in Microsoft Word using VBA.

One desired change to to remove commas that are embedded in numbers while leaving other commas alone. For example:

I like apples, pears, and plums.  See 1,234,567

would become:

I like apples, pears, and plums.  See 1234567

The code looks for a digit followed by a comma followed by a digit. To remove a comma between two digits, the current code loops over all 100 possible cases from 0,0 to 9,9 and does a Find/Replace on each case. So 0,0 becomes 00. Here is the current code:

Sub FindAndReplace()

Dim rng As Range, sFind As String, sRep As String
Dim i As Long, j As Long

For i = 0 To 9
    For j = 0 To 9
        sFind = i & "," & j
        sRep = i & j
        For Each rng In ActiveDocument.StoryRanges
            With rng.Find
                .Text = sFind
                .Replacement.Text = sRep
                .Wrap = wdFindContinue
                .Execute Replace:=wdReplaceAll
            End With
        Next rng
        Next j
    Next i
lbl_Exit:
Exit Sub

I am certain that all this looping over i and j is completely unnecessary and this can be done with a simple wildcard pattern Find, but I don't know enough about the Word Object model to implement this.

UPDATE:

The RexEx solution worked very well. I also worked out a wildcard solution:

Sub CommaKiller()
    Dim rngStory As Range

        For Each rngStory In ActiveDocument.StoryRanges
            With rngStory.Find
                .MatchWildcards = True
                .Text = "([0-9]),([0-9])"
                .Replacement.Text = "\1\2"
                .Wrap = wdFindContinue
                .Execute Replace:=wdReplaceAll
            End With
        Next rngStory
End Sub

Answer 1

You might try using the Microsoft VBScript Regular Expressions 5.5 library:

Then you can build an appropriate regex pattern and have it return the matches in your text, then remove the commas from those matches and return the cleaned version back to the text:

Option Explicit

Public Sub testremovecomma()

Dim txt As String
Dim results As Object
Dim result As Object
Dim r As New RegExp

txt = "I like apples, pears, 2000 6546 and plums.  See 1,234,567"

'a space, followed by:
'between one and three digits followed by a comma (at least one time)
'followed by a group of three digits
r.Pattern = "\s(\d{1,3},+){1,}(\d{3})"

'find all matches, not just the first one
r.Global = True

'get all the matches
Set results = r.Execute(txt)

'for each match, remove the commas and put it back in the text
For Each result In results
    txt = Replace(txt, result, Replace(result, ",", ""))
Next result

Debug.Print txt

End Sub

I threw some random numbers in there for testing that it would only find the numbers containing commas.

This is of course culture-specific and trying to do this with this exact regex in India might not work for all numbers. However, I do think the approach is fine and you can tweak the regex as needed.

I use and recommend this site to test regex:

https://regex101.com/