Excel to Word mail merge

Excel to Word mail merge

Didn't find your answer?

Apologies if this has been asked before but I haven't been able to find anything that answers my question.

Several colleagues have come up with the same outcome. After problems with an existing Excel database, we tried setting up a new spreadsheet and entering figures with only two decimal points. Mail merging these into Word we got the first and third "letter" correct but the others have picked up digits maybe ten spaces after the decimal point.

We have tried everything we can think of to cure this.

It doesn't seem possible.

Can anyone help.

Many thanks in advance.
ian mcgregor

Replies (3)

Please login or register to join the discussion.

avatar
By Richard Willis
07th Sep 2007 14:07

Format cells in the Excel?
Hi Ian

Apologies if this is obvious and already tried but I would first look at the cell format (format/cells/general tab/number) of the source data in Excel. This should be set to 2 decimals.

You then, more importantly, need to look at the source of the actual data. If ANY of the output fields are the result of division (or multiplying by a decimal) anywhere back up the line, then these formulae need to include the '=ROUND' function in order to return the number rounded to 2dp. Otherwise Excel will store the full extension of decimals regardless of the number of dp's displayed.

Thanks (0)
avatar
By john.radford.tenon
07th Sep 2007 16:50

field codes
Its a problem in the way Word picks up the data. A step by step guide below for the more common number formats :

Step 1: Right click on the field and a menu appears
Step 2: Click Toggle field codes
Something similar to this will appear:
{ MERGEFIELD "Number" }
Step 3: Put in formatting codes
A ‘Switch’ needs to be added to the code. Examples shown below
Format required: Field code
Example output (+ve; -ve; zero)
2 decimal places and comma after thousands
{ MERGEFIELD "Number" \# “,##0.00” }
2,323.56 or
-2,323.56 or
0.00
No decimal places (results appear rounded)
{ MERGEFIELD "Number" \# “,##0” }
2,324 or
-2,324 or

Currency
{ MERGEFIELD "Number" \# “£,##0.00” }
£2,323.56 or
£-2,323.56 or
£ 0.00
Negatives in brackets
{ MERGEFIELD "Number" \# “,##0.00;(,##0.00)” }
2,323.56 or
(2,323.56) or
0.00
Zero appears as nil
{ MERGEFIELD "Number" \# “,##0.00;(,##0.00);nil” }
2,323.56 or
(2,323.56) or
nil
Step 4: view results
Right click the field again but this time click Update field. Click the button to view the data to check the format appears as required.

Hope this helps!!

Thanks (0)
avatar
By AnonymousUser
10th Sep 2007 13:15

Thanks John - problem solved!
Thanks also to Richard.

Formatting was correct in Excel.

Thanks (0)