Number formatting
Author Message
Number formatting

Please, will someone help me create a macro to search long documents for big
numbers, typically between 100,000 and 999,999,999?  I want to convert these
numbers by dividing by them 1,000,000 and then rounding to produce a number
similar to "12.34 million" (to 2 digits right of the decimal)   The
resulting number cannot begin with a decimal point, so a zero needs to be
inserted before the decimal if the final number is less than a whole number.

For example: 56,567,303 divided to 1,000,000 equals 56.567303, which rounds
to "56.57 million".

I am new to macros, and have been unable to get anything to work correctly.

Any help would be appreciated.

boB

Mon, 11 Jul 2005 05:10:42 GMT
Number formatting
Hi Bob,

The following does what you want:

Selection.Find.ClearFormatting
With Selection.Find
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Do While .Execute(FindText:="[0-9]{1,},[0-9]{3},[0-9]{3}",
Wrap:=wdFindContinue, Forward:=True) = True
Selection.Range.Text = Format(Int(Selection.Range.Text) /
1000000, "#.00") & " million"
Loop
Do While .Execute(FindText:="[0-9]{3},[0-9]{3}",
Wrap:=wdFindContinue, Forward:=True) = True
Selection.Range.Text = Format(Int(Selection.Range.Text) /
1000000, "0.00") & " million"
Loop
End With

Please post any response to the newsgroups for the benefit of others who may

Hope this helps,
Doug Robbins - Word MVP

Quote:

> Please, will someone help me create a macro to search long documents for
big
> numbers, typically between 100,000 and 999,999,999?  I want to convert
these
> numbers by dividing by them 1,000,000 and then rounding to produce a
number
> similar to "12.34 million" (to 2 digits right of the decimal)   The
> resulting number cannot begin with a decimal point, so a zero needs to be
> inserted before the decimal if the final number is less than a whole
number.

> For example: 56,567,303 divided to 1,000,000 equals 56.567303, which
rounds
> to "56.57 million".

> I am new to macros, and have been unable to get anything to work
correctly.

> Any help would be appreciated.

> boB

Wed, 13 Jul 2005 18:01:32 GMT
Number formatting
Thanks, Doug, for solving my problem.  It works perfectly.

boB

Quote:
> Hi Bob,

> The following does what you want:

>     Selection.Find.ClearFormatting
>     With Selection.Find
>         .Replacement.Text = ""
>         .Forward = True
>         .Wrap = wdFindContinue
>         .Format = False
>         .MatchCase = False
>         .MatchWholeWord = True
>         .MatchAllWordForms = False
>         .MatchSoundsLike = False
>         .MatchWildcards = True
>         Do While .Execute(FindText:="[0-9]{1,},[0-9]{3},[0-9]{3}",
> Wrap:=wdFindContinue, Forward:=True) = True
>             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> 1000000, "#.00") & " million"
>         Loop
>         Do While .Execute(FindText:="[0-9]{3},[0-9]{3}",
> Wrap:=wdFindContinue, Forward:=True) = True
>             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> 1000000, "0.00") & " million"
>         Loop
>     End With

> Please post any response to the newsgroups for the benefit of others who
may
> also be following the thread.

> Hope this helps,
> Doug Robbins - Word MVP

> > Please, will someone help me create a macro to search long documents for
> big
> > numbers, typically between 100,000 and 999,999,999?  I want to convert
> these
> > numbers by dividing by them 1,000,000 and then rounding to produce a
> number
> > similar to "12.34 million" (to 2 digits right of the decimal)   The
> > resulting number cannot begin with a decimal point, so a zero needs to
be
> > inserted before the decimal if the final number is less than a whole
> number.

> > For example: 56,567,303 divided to 1,000,000 equals 56.567303, which
> rounds
> > to "56.57 million".

> > I am new to macros, and have been unable to get anything to work
> correctly.

> > Any help would be appreciated.

> > boB

Tue, 19 Jul 2005 11:00:44 GMT
Number formatting
Oops!  A small problem...

Is it possible to change the formatting of the numbers that result from the
following code so the decimal point is conditional?

Currently, all numbers are followed by a decimal point, even whole numbers
with no digits to the right of the decimal point.  For example, I would like
to eliminate the decimal in \$3. million to read \$3 million.

Thanks again for any help.

Quote:

> > Hi Bob,

> > The following does what you want:

> >     Selection.Find.ClearFormatting
> >     With Selection.Find
> >         .Replacement.Text = ""
> >         .Forward = True
> >         .Wrap = wdFindContinue
> >         .Format = False
> >         .MatchCase = False
> >         .MatchWholeWord = True
> >         .MatchAllWordForms = False
> >         .MatchSoundsLike = False
> >         .MatchWildcards = True
> >         Do While .Execute(FindText:="[0-9]{1,},[0-9]{3},[0-9]{3}",
> > Wrap:=wdFindContinue, Forward:=True) = True
> >             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> > 1000000, "#.00") & " million"
> >         Loop
> >         Do While .Execute(FindText:="[0-9]{3},[0-9]{3}",
> > Wrap:=wdFindContinue, Forward:=True) = True
> >             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> > 1000000, "0.00") & " million"
> >         Loop
> >     End With

> > Please post any response to the newsgroups for the benefit of others who
> may
> > also be following the thread.

> > Hope this helps,
> > Doug Robbins - Word MVP

Sat, 23 Jul 2005 02:03:01 GMT
Number formatting
Hi Bob,

The following modified code will do what you want:

Dim numerals As String
Selection.Find.ClearFormatting
With Selection.Find
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Do While .Execute(FindText:="[0-9]{1,},[0-9]{3},[0-9]{3}",
Wrap:=wdFindContinue, Forward:=True) = True
numerals = Format(Int(Selection.Range.Text) / 1000000, "#.00")
If Right(numerals, 2) = "00" Then
numerals = Format(Int(numerals))
End If
Selection.Range.Text = numerals & " million"
Loop
Do While .Execute(FindText:="[0-9]{3},[0-9]{3}",
Wrap:=wdFindContinue, Forward:=True) = True
Selection.Range.Text = Format(Int(Selection.Range.Text) /
1000000, "0.00") & " million"
Loop
End With

Please post any response to the newsgroups for the benefit of others who may

Hope this helps,
Doug Robbins - Word MVP

Quote:
> Oops!  A small problem...

> Is it possible to change the formatting of the numbers that result from
the
> following code so the decimal point is conditional?

> Currently, all numbers are followed by a decimal point, even whole numbers
> with no digits to the right of the decimal point.  For example, I would
like
> to eliminate the decimal in \$3. million to read \$3 million.

> Thanks again for any help.

> > > Hi Bob,

> > > The following does what you want:

> > >     Selection.Find.ClearFormatting
> > >     With Selection.Find
> > >         .Replacement.Text = ""
> > >         .Forward = True
> > >         .Wrap = wdFindContinue
> > >         .Format = False
> > >         .MatchCase = False
> > >         .MatchWholeWord = True
> > >         .MatchAllWordForms = False
> > >         .MatchSoundsLike = False
> > >         .MatchWildcards = True
> > >         Do While .Execute(FindText:="[0-9]{1,},[0-9]{3},[0-9]{3}",
> > > Wrap:=wdFindContinue, Forward:=True) = True
> > >             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> > > 1000000, "#.00") & " million"
> > >         Loop
> > >         Do While .Execute(FindText:="[0-9]{3},[0-9]{3}",
> > > Wrap:=wdFindContinue, Forward:=True) = True
> > >             Selection.Range.Text = Format(Int(Selection.Range.Text) /
> > > 1000000, "0.00") & " million"
> > >         Loop
> > >     End With

> > > Please post any response to the newsgroups for the benefit of others
who
> > may
> > > also be following the thread.

> > > Hope this helps,
> > > Doug Robbins - Word MVP

Sat, 23 Jul 2005 19:32:00 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages