Formatting numbers with user confirmation (find and replace + wildcards)

[lachouffe]

Hi all,

I've been trying to modify a code all day and I've had very little luck :banghead:, so I've come here seeking your guidance and expertise :hi:.

I found this very useful code (edited slightly) for converting 1234 to 1 234 (hard space as thousands separator) with the user's approval. I'd now like to expand the functionalities of the macro to also change 1,234 and 1 234 (normal space as thousands separator) to 1 234 (hard space as thousands separator). The user should be prompted for each change. I've tried several approaches and they've all failed miserably.

I can't work out if there's a way to insert something into the code along the lines of:


With oRng.Find
.Text = "([0-9]{1,}) ([0-9]{3})"
.Replacement.Text = "\1^s\2"

With oRng.Find
.Text = "([0-9]{1,}),([0-9]{3})"
.Replacement.Text = "\1^s\2"


Can anybody help?


Sub ThousandMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "[0-9]{4,}"
.MatchWildcards = True
While .Execute
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng = Format(oRng, "#" & Chr(160) & "###")
End If
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

The end result should be:

Before:

100,000
10 000
999 000
24000

After:

100 000
10 000
999 000
24 000

[gmaxey]

Something like this might work provided you don't have two separate numerical terms separated by spaces:


Sub ThousandMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "[0-9, ]{4,}"
.MatchWildcards = True
While .Execute
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng.Text = Replace(oRng.Text, " ", ",")
oRng.Text = Format(oRng.Text, "#,###")
oRng.Text = Replace(oRng.Text, ",", Chr(160))

End If
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

[lachouffe]

Hi Greg,

Thank you very much for your help and thanks also for the original macro. I was really impressed when I saw your solution. Unfortunately, the code's still having teething problems. "Format" seems to be changing the numbers considerably.

Please note: I also had to change:

.Text = "[0-9, ]{4,}"

to

.Text = "[0-9, ]{4;}"

As my system settings appear to be different here.

The problem I've encountered with the following code is that:

9999
999,999
99 999
1 000
10,000
1000

Becomes:

9.999
1.000
100
1
10
1.000


Sub ThousandMacro_test()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.range
Set oRng = ActiveDocument.range
With oRng.Find
.Text = "[0-9, ]{4;}"
.MatchWildcards = True
While .Execute
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng.Text = Replace(oRng.Text, " ", ",")
oRng.Text = Format(oRng.Text, "#,###")
oRng.Text = Replace(oRng.Text, ",", Chr(160))

End If
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

I can edit the code as follows and make some progress:


Sub ThousandMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.range
Set oRng = ActiveDocument.range
With oRng.Find
.Text = "[0-9, ]{4;}"
.MatchWildcards = True
While .Execute
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng.Text = Replace(oRng.Text, " ", ",")
oRng.Text = Replace(oRng.Text, ",", Chr(160))
End If
oRng.Collapse wdCollapseEnd
Wend
End with
End Sub

With this change the macro successfully deals with numbers with a space and numbers with a comma:

9999
999,999
99 999
1 000
10,000
1000

9999
999 999
99 999
1 000
10 000
1000

[lachouffe]

I seem to have fixed it as follows:


Sub ThousandMacro3()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.range
Set oRng = ActiveDocument.range
With oRng.Find
.Text = "[0-9, ]{4;}"
.MatchWildcards = True
While .Execute
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng.Text = Replace(oRng.Text, " ", "")
oRng.Text = Replace(oRng.Text, ",", "")
oRng = Format(oRng, "#" & Chr(160) & "##0")
End If
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

[lachouffe]

As is usually the case, I've come across some problems in the practical application of the macro. Would it be possible to modify the above code so that it'll only search within selected text? I.e. it'll run in a paragraph if a paragraph is selected, or a whole document if all the text is selected. I think the main issue is going to be that I also want to have the "oRng.Select" line in, so I can see what I'm agreeing to format. I was thinking that a less ideal but maybe more feasible solution would be to mark the start and end of the text I'd like to apply the macro to with a highlight and then run it and remove the highlight. Any ideas? Thanks!

[gmayor]

That's a simple enough fix


Sub ThousandMacro4()
Dim oRng As Word.Range
Dim oFind As Word.Range
Set oRng = Selection.Range
Set oFind = Selection.Range
With oRng.Find
.Text = "[0-9, ]{4;,}"
.MatchWildcards = True
While .Execute
If oRng.InRange(oFind) Then
oRng.Select
If MsgBox("Do you want to format this instance", vbQuestion + vbYesNo, "FORMAT") = vbYes Then
oRng.Text = Replace(oRng.Text, " ", "")
oRng.Text = Replace(oRng.Text, ",", "")
oRng = Format(oRng, "#" & Chr(160) & "##0")
End If
oRng.Collapse wdCollapseEnd
End If
Wend
End With
End Sub

[lachouffe]

Thanks, Graham. It works like a charm! :D