Excel Autoformat and Mail Merge Error

[brogan]

Hopefully this should be a simple problem for somebody to help, I would be greatful as it is causing me problems.

I have a simple formula that works 82.5% of a monetary value, as seen below:

=SUM(E2*0.825)

I have set up the whole column so that it shows the result to no decimal point and with a comma included which works fine.

I then link this Excel document to a Word document so that I can Mail Merge these figures into letters.

However, when I preview the letters before printing I get figures such as £147595.89999999999 and with no comma.

How do I get round this? For the above example I would want to show £147,600 (rounded up and with the comma). Word seems to take the unformatted value?

Regards Mark

 

[SkipVought]

Hi,

Format in Excel, changes NOTHING in the underlying data. It is merely a DISPLAY convenience IN EXCEL.

When you query the data, you get the data that is there. You must FORMAT the field in Word, using the FormField code.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[macropod]

Hi Mark,

To fix this, select your mailmerge field in Word and press Shift-F9 to expose the code. It'll look something like:
{MERGEFIELD Excelval}
Then add a numeric picture switch to the field, thus:
{MERGEFIELD Excelval \# £,0.00}
This will give you a currency value to 2 decimal places and a thousands separator.
When you're done, press F9 to update the field and run your mailmerge.

Skip:

You must FORMAT the field in Word, using the FormField code

I think I know what you meant to say, but it has nothing to do with formfields.

Cheers



[MS MVP - Word]

 

[SkipVought]

MERGEFIELD, rather then FORMFIELD, a field nonethelss.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]