Formatting lost in Mail Merge (Word / Excel XP)

  • Hi everybody!

    In Office 2000 the formatting in Excel (e.g. Currency, %, etc.) is drawn into Word during the mail merge, BUT it is COMPLETELY lost in XP.

    The Help file (Topic: Format merged data) reads as follows:

    "To format merged data, you must format the merge fields in the main document. Don't format the data in the data source, because its formatting isn't retained when you merge the data into the document.


    Why, it seems as though MS has gone backwards, say what!

    Can anyone help me to keep the formatting in Excel in Word? It changed 40% to 0.4000000000000002, for example, and $2, 123, 123.00 to 2123123.00000000.

    Help, please?



  • I think you have to format the merge fields within Word to make that happen.

    Once you set up your document with the merge fields, you should have things that look like <"fieldname"&gt;, etc.

    Press Alt-F9 to display the Field Codes and the merge fields should change view and look like {MERGEFIELD fieldname}

    You can then add the switches to format the way the field is displayed in Word. For example if your field name is F1 and you want it to display in Currency format change {MERGEFIELD F1} to read {MERGEFIELD F1 \\# $###,###,##0.00}

    Press Alt-F9 again to go back to the original field display.

    For a listing of the switches you can use, look up "General Switches" is the Word Help section.

    I couldn't get it to change .40000 to 40%. You can use {\\# ##0.00%} to make the number show with decimal points and percent sign, but it still shows up as 0.40% - may have to multiply by 100 in excel before merge.



  • Correction . . . .

    There's only supposed to be 1 backslash character in the switch . . . some days I just can't type.... :spin:

  • Hi Ralph!

    My expertise in formatting those Word Switch Fields is such that your help had me battling along, so I thought of simply asking Microsoft for their opinion, also telling the lady that I miss the previous version.

    This is what I got:

    Go through the whole mail merge wizard "thing" on the Tools menu until you get to Select Data Source, where you find the Excel workbook you want to use as data source and click Open.

    In the Confirm Data Source dialog box, click MS Excel Worksheets via DDE (*.xls) and then click OK.

    Then simply carry on with the rest of the wizard (print range vs. etc. etc.) and click OK

    The data is then transferred into Word the same way as with Word 2000, complete with formatting of Excel.

    No need to format switches and stuff!

    Cool, eh?

    So now I do not think that MS are going backwards. Seems to be a lot more powerful. Why and how I do not know, however.

    Thanks for the assistance, Ralph!

    Hope this helps you in some way, too. It is a lot easier for me.


  • Hi Harry -

    Thanks for the update. It's always a victory when you get a good answer out of the M$ tech support people! Also sounds like the people who write the Help files need to know the program a little better (but what else is new...)

    Will keep the DDE thing in mind for the next time a use Word mailmrege.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!