Number thousand seperator

[Nora Najem]

Hello, can any one help me writing a word macro to do the following:

Put comma (as thousand seperator) in numbers using find and replace.
or, as auto correct , so that when typing 3 digits, a comma would be
inserted automatically.

 

[Helemut Weber]

Hi Nora,
What kind of numbers have you got?
123213123
123123111123.334
0.3
0.3444798734
10.3444798734

 

[Malcolm Smith]

Nora

What if this code were to be used in the countries where the comma is a
decimal separator and the point is the thousands separator. For example,
your company opens an office in Europe...

Surely, you will also want to consider that?

- Malc

 

[Helmut Weber]

Hi Nora,
can't even write my own name "Helmut", ;-)
like this:
Sub test113()
Dim sNmb As String
Dim oRng As Range
Set oRng = ActiveDocument.Range
Resetsearch
With oRng.Find
.Text = "[0-9]{3,}"
.MatchWildcards = True
While .Execute
sNmb = oRng.Text
sNmb = Format(sNmb, "#,#")
oRng.Text = sNmb
oRng.Start = oRng.End
oRng.End = ActiveDocument.Range.End
Wend
End With
Resetsearch
End Sub

Sub Resetsearch()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End With
End Sub
---
Note, that the thousands seperator depends on the localization.
As well as the list seperator "," in ".Text = "[0-9]{3,}""
But I guess, "," should work with American English in both cases.

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Helmut Weber]

Hi Nora,
sorry, could not resist,
if "the numbers may reach billions" then they are of type "long".
Have a nice day.
Helmut Weber, MVP

Gruss
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Nora Najem]

I am back

Dear Helmut

Thank you for the code.

I tested the code and got the following prompt:

"Run-time error 5560 The find What test contains a Pattern Match expression
which is not valid". When debugged the "While.Execute" is highlithed.

Is there any fix for this.

Another thing we have to take into consideration, which is the years format
which should be execluded from the search.

Thanking you in advance

(by the way, I am not good in programming, I wish I were, so if you could
advise me where I can find tutorials and ebooks which may help in VBA for
beginners, I would appreciate it very much.)

 

[Helmut Weber]

Hi Nora,

"Run-time error 5560 The find What test contains
a Pattern Match expression which is not valid".

that is due to localization, which I abhor.
"[0-9]{3,}" works with US-English.
"[0-9]{3;}" works at least with German and Dutch.
Documentation on differences between localization is hard to find.
MsgBox Application.International(wdListSeparator) might tell you,
what your listseperator is.
Excluding dates from being reformatted, depends on the date format,
wich <gr> depens on localization again.
You may check, whether the found string contains a seperator such as
"/" or "." and exclude it from reformatting, like this:
Sub test113()
Dim sNmb As String
Dim oRng As Range
Set oRng = ActiveDocument.Range
Resetsearch
With oRng.Find
.Text = "[0-9]{3,}"
.MatchWildcards = True
While .Execute
sNmb = oRng.Text
if (instr(sNmb, "/") = 0) and (instr(sNmb, ".") = 0) then ' !!!
sNmb = Format(sNmb, "#,#")
oRng.Text = sNmb
endif
oRng.Start = oRng.End
oRng.End = ActiveDocument.Range.End
Wend
End With
Resetsearch
End Sub

 

[Nora Najem]

Dear Helmut

Thank you. The code worked just fine. However, if my document has table in
it, the code will hang.
I also tried to use "Selection" istead of "Active Document", which in some
cases would be better, but I didn't succeed. Is it too much if I asked for
your help again?

Thank you again and best regards

Hi Nora,

"Run-time error 5560 The find What test contains
a Pattern Match expression which is not valid".

that is due to localization, which I abhor.
"[0-9]{3,}" works with US-English.
"[0-9]{3;}" works at least with German and Dutch.
Documentation on differences between localization is hard to find.
MsgBox Application.International(wdListSeparator) might tell you,
what your listseperator is.
Excluding dates from being reformatted, depends on the date format,
wich <gr> depens on localization again.
You may check, whether the found string contains a seperator such as
"/" or "." and exclude it from reformatting, like this:
Sub test113()
Dim sNmb As String
Dim oRng As Range
Set oRng = ActiveDocument.Range
Resetsearch
With oRng.Find
.Text = "[0-9]{3,}"
.MatchWildcards = True
While .Execute
sNmb = oRng.Text
if (instr(sNmb, "/") = 0) and (instr(sNmb, ".") = 0) then ' !!!
sNmb = Format(sNmb, "#,#")
oRng.Text = sNmb
endif
oRng.Start = oRng.End
oRng.End = ActiveDocument.Range.End
Wend
End With
Resetsearch
End Sub
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Helmut Weber]

Hi Nora,
tables bear unknown secrets. I bet, even at MS nobody knows them all.
I found myself dumbfounded when I checked numbers in tables.
IMHO, software is at the border of un-managebility,
of over-complexity. There are workarounds and workarounds,
and nobody knows why some work and why some don't.
The best I can suggest is, processing tables seperately, at first.
Like this:
Sub FormatNumbersinTabels()
Dim sNmb As String
Dim oTbl As Table
Dim oCll As Cell
Resetsearch
For Each oTbl In ActiveDocument.Tables
For Each oCll In oTbl.Range.Cells
oCll.Select
With Selection.find
.Text = "[0-9]{3;}" ' ; = german, dutch etc. !!!!!!!!!!!
.MatchWildcards = True
While .Execute
If Selection.Information(wdWithInTable) = False Then
Exit Sub '!!!
End If
sNmb = Selection.Text
If (InStr(sNmb, "/") = 0) And (InStr(sNmb, ".") = 0) Then
sNmb = Format(sNmb, "#,#")
Selection.Text = sNmb
End If
Wend
End With
Next
Next
Resetsearch
End Sub
HTH.
Where are you from, because of the listseperator?

 

[Nora Najem]

Dear Helmut
Thank you for the code, it worked fine in tables. However, this means that I
have to use two macros one for tables and the other for the text, which in
this case we should use the seach in "Selection" instead of the entire
document. How can we revise the macro to use "Selection" instead of "Active
Document".

Your help would be much appreciated.

By the way, I am from Lebanon and I use comma (,) as list seperator.

 

[Helmut Weber]

Hi Nora,
so often I was blamed for using selection instead of range.
So I decided to use ranges. But he complications are endless.
After hours and hours I arrived at selection again.
Like this:
Sub test103()
Dim sNmb As String ' string representing a number
Selection.ExtendMode = False
Selection.HomeKey unit:=wdStory
Resetsearch
With Selection.find
.Text = "[0-9]{3;}"
.MatchWildcards = True
While .Execute
sNmb = Selection.Text
sNmb = Format(sNmb, "#,#")
Selection.Text = sNmb
Wend
End With
Resetsearch
End Sub
---
My first thoughts on excluding years were simply wrong.
Theoretically it seems impossible, to decide between
"2000 years from now" and "2000 miles from here".
I'd suggest to search your docs for [0-9]{2}.[0-9]{2}.[0-9]{4},
if that is your date format, hide it, e.g. reformat the numbers,
and show hidden text again. Or, still safer, search for dates,
color them in one of 16,000,000 or so variations, exclude
such colored text from replacement, and restore color afterwards.
---
Hope this helps.
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Helmut Weber]

Not

sNmb = Format(sNmb, "#,#") but
sNmb = Format(sNmb, "#,000")

otherwise you will loose trailing "0"s.
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Nora Najem]

Hello Helumut,

I am sorry I didn't have the chance to thank you as I was away in a trip. I
just wanted you to know that you helped me a lot with the code, although I
didn't fix all the problem yet.

What I need is that, I do a selection in a document and run the code on the
selection.

If you still can offer any help, it would be much appreciated. Otherwise
forget it and thank you again for every thing.

Regards
Nora

 

[Helmut Weber]

Hi Nora,
your posting almost got lost in the electronical universe.
I'd suggest, to start a new thread, if there are further questions.
And sometimes it is good, to hear a second opinion.
However, for restricting the search and replace to the selection,
it could look like this, and note, that I have adapted "ResetSearch",
too.
Sub test104()
Dim sNmb As String ' string representing a number
Resetsearch
With Selection.find
.text = "[0-9]{3,}"
.MatchWildcards = True
While .Execute
sNmb = Selection.text
sNmb = Format(sNmb, "#,000")
Selection.text = sNmb
Selection.Collapse direction:=wdCollapseEnd 'more speed
Wend
End With
Resetsearch
End Sub
'---
Sub Resetsearch()
With Selection.find
.ClearFormatting
.Replacement.ClearFormatting
.text = ""
.Replacement.text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With
End Sub
This is to format numbers without seperators.
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/