Using Excel as a mail merge data source

[dasadler]

I have the worksheet with a column of amounts:

120
80
90
110

these are formatted as currency:
$120.00
$80.00
$90.00
$110.00

The name of the column is AMOUNT and I wanted the mail merge to pull it in with two decimal places (120.00, 80.00, so on) I tried copy & paste values but that changes nothing.

[HansV]

Do you want to do a mail merge to Word?
Right-click the Amount merge field in Word.
Select Toggle Field Codes from the popup menu or press Shift+F9.
You'll see something like { MERGEFIELD AMOUNT \* MERGEFORMAT }
Change it so that it looks like { MERGEFIELD AMOUNT \# "#,##0.00" } or { MERGEFIELD AMONUT \# "$#,##0.00" } depending on which format you prefer.
PRess F9 to hide the field code and update the result.

[macropod]

I'd suggest coding the mergefields as:
{ MERGEFIELD AMOUNT \# ",0.00" } or { MERGEFIELD AMONUT \# "$,0.00" }
This avoids any unwanted padding in the output.

[HansV]

Thanks, Paul. I always find it annoying that Office programs don't use the same formatting codes. In Excel, Access and also in VBA, # is completely suppressed if there is no corrseponding digit.

[macropod]

Vive la difference!

These inconsistencies exist even within Office apps - IIRC, Excel's vba DateDiff vba uses completely different logic to the equivalent DateDif worksheet function.