formatting numbers imported from excel in mailmerge

I am doing a mailmerge in Office 2007 with data imported from an excel
spreadsheet where one of the spreadsheet fields is calculated (to give a rate
of exchange). The problem is that the word documents returns a figure to 10
decimal places. I have tried:
a) putting the calculation in one column and then used ROUNDUP to convert
the number to 2 decimal places and using that column for the field. (Word
just returns 10 decimals)
b) Changing the document parameters in Word Options _Advanced - General -
confirm file format conversion on open. Same thing
c) Saving the spreadsheet and closing it. Saving the Word Doc and closing
it. Re-opening the word file and re-confirming the spreadsheet. Same thing.
d) Rebooting my laptop and re-opening the files. Same thing.

I am getting v frustrated - going through 300 merged letters and deleting 8
digits (and search and replace is not an option, there are too many
variables) is too horrible to contemplate. This must have happened to other
people and I'm sure there's a solution.... but, boy, would I be incredibly
grateful to know what it is.
thank you.

There is a brilliant solution from Peter Jamieson on 9/9/2009 in an earlier
post. Obviously my search query wasn't great, otherwise I would have found
the solution. For other people with the same problem, look at the post; it
solved my problem and I am grateful for the happy bonus of being able to tap
into other people's expertise and knowledge. thank you.

"CrestaBrab" wrote:

I am doing a mailmerge in Office 2007 with data imported from an excel
spreadsheet where one of the spreadsheet fields is calculated (to give a rate
of exchange). The problem is that the word documents returns a figure to 10
decimal places. I have tried:
a) putting the calculation in one column and then used ROUNDUP to convert
the number to 2 decimal places and using that column for the field. (Word
just returns 10 decimals)
b) Changing the document parameters in Word Options _Advanced - General -
confirm file format conversion on open. Same thing
c) Saving the spreadsheet and closing it. Saving the Word Doc and closing
it. Re-opening the word file and re-confirming the spreadsheet. Same thing.
d) Rebooting my laptop and re-opening the files. Same thing.

I am getting v frustrated - going through 300 merged letters and deleting 8
digits (and search and replace is not an option, there are too many
variables) is too horrible to contemplate. This must have happened to other
people and I'm sure there's a solution.... but, boy, would I be incredibly
grateful to know what it is.
thank you.

Hi CrestaBrab,

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 controlled 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]


"CrestaBrab" wrote in message ...
I am doing a mailmerge in Office 2007 with data imported from an excel
spreadsheet where one of the spreadsheet fields is calculated (to give a rate
of exchange). The problem is that the word documents returns a figure to 10
decimal places. I have tried:
a) putting the calculation in one column and then used ROUNDUP to convert
the number to 2 decimal places and using that column for the field. (Word
just returns 10 decimals)
b) Changing the document parameters in Word Options _Advanced - General -
confirm file format conversion on open. Same thing
c) Saving the spreadsheet and closing it. Saving the Word Doc and closing
it. Re-opening the word file and re-confirming the spreadsheet. Same thing.
d) Rebooting my laptop and re-opening the files. Same thing.

I am getting v frustrated - going through 300 merged letters and deleting 8
digits (and search and replace is not an option, there are too many
variables) is too horrible to contemplate. This must have happened to other
people and I'm sure there's a solution.... but, boy, would I be incredibly
grateful to know what it is.
thank you.