Re: Mail Merge and Incorrect Formatting

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Chi,

To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield. To do this:
.. select your mergefield, which will look something like «Percent»;
.. press Ctrl-F9 to wrap another field around it, thus - { «Percent» };
.. edit the field so that you get {=«Percent»*100 \# 0.00%};
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.

As per my previous post, the number of 0s after the decimal point controls the precision.

--
Cheers
macropod
[Microsoft MVP - Word]


"Chi" <Chi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:95079FDE-9D7C-46A5-88CA-1F08043985C8@xxxxxxxxxxxxxxxx
Hi Macropod,

Excellent! Thank you very much! The problem is fixed!

Would you please also show me how to fix the % formatting?

In Excel is -17.0% and it appeared -0.1695323549869793 in Word

Thank you!
Chi

"macropod" wrote:

Hi Chi,

What you're seeing is what is actually stored in the Excel cell.

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
.. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.

Note: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
.. \# 0 for rounded whole numbers
.. \# ,0 for rounded whole numbers with a thousands separator
.. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
.. \# $,0 for rounded whole dollars with a thousands separator
.. \# $,0.00;($,0.00);'-' for currency, with brackets around negative numbers and a hyphen for 0 values


The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed.
Note that this suppresses 0s resulting from empty fields and from fields containing 0s.


--
Cheers
macropod
[Microsoft MVP - Word]


"Chi" <Chi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:833496AC-67E8-4FE1-AD8F-C1A7306006BB@xxxxxxxxxxxxxxxx
> Hi,
>
> I merge my letter (word) with Excel (data source). It runs fine, but the
> formatting of the number is changed in the mail merge's result.
>
> Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in
> the final letter.
>
> In the Mail Merge Recipients window, I also see the number's formatting
> changed in there.
>
>
>
> Please help
> Thanks
> Chi
>



.

Relevant Pages

  • Re: Mail Merge and Incorrect Formatting
    ... "macropod" wrote: ... To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word/Excel
    ... You're getting that result because that's what's really in the Excel cells. ... You can control how the data are displayed by adding a numeric picture switch to the mergefield. ...
    (microsoft.public.word.docmanagement)
  • Re: Mail Merge and Incorrect Formatting
    ... To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Mail Merge Formating Issues
    ... To control number & currency formatting in Word, add a numeric picture switch to the mergefield. ... To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield. ...
    (microsoft.public.excel.misc)
  • Re: Mail Merge Switch
    ... To control number & currency formatting in Word, add a numeric picture switch to the mergefield. ... To control percentage formatting in Word, you need to add a formula and numeric picture switch to the mergefield. ...
    (microsoft.public.word.docmanagement)