Maintaining formatting of data when merging with Word files

[Maria]

How do you keep the formatting for data in Excel when the
data is being used in a Word letter merge? The data is
formatted as 2 decimal points but when merged in Word the
data expands to 6 decimal places or more.

I've tried copy and paste special as value; round
function; saving as a comma separated value (csv) file....

Savings as a CSV seemed to work except when you want to
go back and edit the data file, it doesn't recognize the
file type and the formatting is lost when saved as an
Excel file again.

 

[JE McGimpsey]

I would add the format switch in Word's merge fields.

 

[Dave Peterson]

From a previous post:


You could use some helper cells and create strings for each field--then use
those helper values:

=text(a1,"dd-mmm-yyyy")
=text(b1,"0.0")
=text(c1,"#,##0.00")

or you could do some magic at the MSWord side.

Debra Dalgleish posted this for a different question:

In the Mail Merge, after you select your Excel file as a data source,
you should see a 'Confirm Data Source' dialog box.
(If you don't see the dialog box, change the setting in Word --
under Tools>Options, General -- add a check mark to
'Confirm Conversion at Open')

From that list, choose 'MS Excel Worksheets via DDE (*.xls)', and your
formatting will be retained.

If you have to connect through a different source, you can format the
fields in the Word document. For example, to specify a number of decimals:

1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
{ MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
For example:
{ MERGEFIELD FieldName \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document