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