Mail Merge and Incorrect Formatting
Chi
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
macropod
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" <C...@discussions.microsoft.com> wrote in message news:833496AC-67E8-4FE1...@microsoft.com...
Chi
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
macropod
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" <C...@discussions.microsoft.com> wrote in message news:95079FDE-9D7C-46A5...@microsoft.com...
dccsvd55
Another solution ... Copy all the necessary data from Excel into a new Word
file. (All the data is copied as text, but is also copied in the correct
number, percentage or date formats. Mark the top row as the header then save
the file. Open Word document for merging and select the new data file. This
works too without having to recode every field in the merge document.
--
Thanks
"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
Chi
It woks wonderful! Sorry I have more questions. In my % field, there is some
"N\A" text so that It got some “! Undefined Bookmark,N". Would you please
show me how to make it displays N\A instead of "! Undefined Bookmark,N".
Thanks
Chi
"macropod" wrote:
> 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%};
Chi
Sorry, I don't kow your name. Your way also works wonderful!!! Thank you for
sharing it .
Chi
Doug Robbins - Word MVP
field.
{ IF { MERGEFIELD PROD } = "N/A" "N/A" "{ = { MERGEFIELD PROD } * 100 \#
"0%" }" }
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Chi" <C...@discussions.microsoft.com> wrote in message
news:9ECE6FCB-29FC-4959...@microsoft.com...
Caykiwi
Thanks
"macropod" wrote:
> 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%};