Mail Merge with Excel and Word

[Larry]

I have a very large spreadsheet that contains a column of
amounts representing dollars and cents, another column
which multiplies that column by a percentage and then a
resulting column that adds the two previous columns
together. I have formatted those columns to
be "general", "accounting", "currency", and "special" and
have set it to be two decimals only. The columns appear
in the spreadsheet with only two decimals but when i
merge the final column number in to a word document, i
get all the calculated decimals (5 or 6). How do I get
rid of the extra decimal points so that the letter shows
a dollar amount with only 2 decimals. Anyone's help
would be appreciated.

 

[Guest]

I am not sure if this will work, but I had to do somethign similar when exporting to an Access database. For the final column use the "round" function and set it to 2 decimals. The other way is only formatting, but his changes the resulting number.

Give it a try, although I am no expert.


----- Larry wrote: -----

I have a very large spreadsheet that contains a column of
amounts representing dollars and cents, another column
which multiplies that column by a percentage and then a
resulting column that adds the two previous columns
together. I have formatted those columns to
be "general", "accounting", "currency", and "special" and
have set it to be two decimals only. The columns appear
in the spreadsheet with only two decimals but when i
merge the final column number in to a word document, i
get all the calculated decimals (5 or 6). How do I get
rid of the extra decimal points so that the letter shows
a dollar amount with only 2 decimals. Anyone's help
would be appreciated.

 

[Domenic]

I have a very large spreadsheet that contains a column of
amounts representing dollars and cents, another column
which multiplies that column by a percentage and then a
resulting column that adds the two previous columns
together. I have formatted those columns to
be "general", "accounting", "currency", and "special" and
have set it to be two decimals only. The columns appear
in the spreadsheet with only two decimals but when i
merge the final column number in to a word document, i
get all the calculated decimals (5 or 6). How do I get
rid of the extra decimal points so that the letter shows
a dollar amount with only 2 decimals. Anyone's help
would be appreciated.

Hi Larry,

Try adding this numeric switch to your mergefield in your Word document:

\# "#,###.00"

OR

\# "$#,###.00", if you want it formatted with the dollar sign.

The complete field code should look something like this:

{MERGEFIELD your-field-name \# "$#,###.00"}

Let me know if you need further help!

 

[Debra Dalgleish]

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