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
also be following the thread.

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
also be following the thread.

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  
 
 [ 5 post ] 

 Relevant Pages 

1. How to convert number format to date format

2. "Accounting" Number Format

3. Access 97 and signed number formats

4. Number format and regional settings

5. microsoft outlook phone number formatting

6. Number format in Access 2000

7. Number Format in UserFrom Lables and Text Boxes

8. Problems with number format

9. Change Phone Number Format

10. Number format

11. Telephone number formatting

12. Simple quesiton on Number Format .. ^_^

 

 
Powered by phpBB® Forum Software