Mail Merge Formating Issues

[DataGuy]

I hope this is a simple fix. I am merging some data from Excel into a form
built in Word.

I have fomated the cells in Excel but when the merge to Word they are not
coming out correctly.

EX - Number are formated in Excel with commas but merge to Word with no
commas and extend decimal places. Dates are formated in Excel as July 13,
2009 but merge to Word as 071309.

What do I need to do to get this merge to format properly in Word? Thanks
so much for your help.

 

[Joel]

I can't tell your exact steps you are peforming. You may be able to solve
the problem by copying the data and using PasteSpecial choosing text. You
may want to copy the data first to a new worksheet using the paste special
method and then performing the mail merge from the new worksheet instead of
the origianl worksheet.

 

[macropod]

Hi DataGuy,

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.

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 controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.


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.

Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.


To get the date format you want, you can add a formatting picture switch. In Word:
.. select the mergefield;
.. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's
name;
.. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM
yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months.
.. position the cursor anywhere in this field and press F9 to update it.

When you're done with the above, run your mailmerge.

 

[Pauline Palmer]

Thanks Guys this was a VERY BIG help - I had spent day's trying to get it right

Hi DataGuy,

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.

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 controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.


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.

Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.


To get the date format you want, you can add a formatting picture switch. In Word:
.. select the mergefield;
.. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's
name;
.. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM
yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months.
.. position the cursor anywhere in this field and press F9 to update it.

When you're done with the above, run your mailmerge.

--
Cheers
macropod
[Microsoft MVP - Word]

I hope this is a simple fix. I am merging some data from Excel into a form
built in Word.

I have fomated the cells in Excel but when the merge to Word they are not
coming out correctly.

EX - Number are formated in Excel with commas but merge to Word with no
commas and extend decimal places. Dates are formated in Excel as July 13,
2009 but merge to Word as 071309.

What do I need to do to get this merge to format properly in Word? Thanks
so much for your help.