Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-08-2012, 04:51 PM
Tamarrissa Tamarrissa is offline Mail Merge Number Error Windows 7 64bit Mail Merge Number Error Office 2007
Novice
Mail Merge Number Error
 
Join Date: Nov 2012
Posts: 3
Tamarrissa is on a distinguished road
Default Mail Merge Number Error

Hi,



I'm trying to mail-merge an Excel document into a Word letter. All the numbers in Excel are in 0.00 (accounting) format (and none of them have 'extra digits' that aren't visible, I triple checked this ), but when it merges into the Word document, it merges like this: 14.300000000000001 (should just be 14.30) or 21.739999999999998 (should just be 21.74). It does this for every number.
Any help at all would be greatly appreciated!!
Reply With Quote
  #2  
Old 11-08-2012, 05:00 PM
macropod's Avatar
macropod macropod is offline Mail Merge Number Error Windows 7 64bit Mail Merge Number Error Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Tamarissa,

What you're now seeing is the raw values in Excel. 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.

Note1: 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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-09-2012, 09:01 AM
Tamarrissa Tamarrissa is offline Mail Merge Number Error Windows 7 64bit Mail Merge Number Error Office 2007
Novice
Mail Merge Number Error
 
Join Date: Nov 2012
Posts: 3
Tamarrissa is on a distinguished road
Default

Thank you so much Paul, that worked fantastically !
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Number Error Mail Merge to create specific number of table rows flackend Mail Merge 4 12-01-2023 02:49 PM
Mail Merge Number Error Automatically split Mail Merge based on number of pages SaneMan Mail Merge 1 12-03-2011 01:11 AM
Mail Merge Number Error Mail Merge, Number to 2 Decimal Places Vampy99 Mail Merge 7 09-25-2011 05:41 AM
Mail Merge Number Error Mail merge number fields outputting inconsistently rekcots Mail Merge 2 05-18-2011 06:32 AM
Mail merge error? SRE Outlook 0 01-11-2010 05:54 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft