Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    mountains, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Formatting a Merge Field from an Excel Database Office 2007

    I am trying to Mail Merge some data from an Excel Spreadsheet into a Word Document. The number in the spreadsheet contains several places to the right of the decimal that I do not need in the Mail Merged document. I would rather not change the values in the spreadsheet if I can help it.

    As a last resort I can set up additional columns and use the Round function to round to the appropriate number of places.

    Am wondering if there is a way to round the data in the merged field. Example (Spreadsheet # is 1.12345678, I would like the Mail Merged document to be 1.123).

    Thanks in advance for any advice.

    Larry P

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,480 Times in 1,346 Posts

    You need to format the merge field in the master document.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 426 Times in 352 Posts
    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.

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts