Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / March 2006

Tip: Looking for answers? Try searching our database.

How do I make a mail merged numbers print decimals to 2 places?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brig_view - 09 Mar 2006 05:19 GMT
I have an excel file used in a mail merge, the file has been formatted as
numbers to 2 decimal places eg. 10.01, 10.03. When I use this file in the
mail merge I get 10.03 represented as 10.0299999. How do I make word
represent the numbers I have in the excel file?
Brig_view - 09 Mar 2006 05:52 GMT
Thanks the solution was posted on this site. You have to format the field in
word by toggling between the fields and adding \#00.00. Why does excel
represent the number correctly and word does not until it's formatted?

> I have an excel file used in a mail merge, the file has been formatted as
> numbers to 2 decimal places eg. 10.01, 10.03. When I use this file in the
> mail merge I get 10.03 represented as 10.0299999. How do I make word
> represent the numbers I have in the excel file?
Graham Mayor - 09 Mar 2006 07:32 GMT
Word reads only the underlying raw data and not the formatted data - see
http://www.gmayor.com/formatting_word_fields.htm

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Thanks the solution was posted on this site. You have to format the
> field in word by toggling between the fields and adding \#00.00. Why
[quoted text clipped - 6 lines]
>> 10.0299999. How do I make word represent the numbers I have in the
>> excel file?
Peter Jamieson - 09 Mar 2006 09:44 GMT
> Why does excel
> represent the number correctly and word does not until it's formatted?

"Why" is always a difficult question to answer, particularly when you are
not the author of the software concerned. But...

You have almost answered your own question:
 a. Excel stores the  nmber internally using a floating point
representation which is not necessarily exactly what you typed in
 b. Excel either applies default cell formats or one you explicitly apply
yourself, correcting the problem
c. when Word connects to Excel to get the data using OLEDB (the default
mechanism in Word 2002/2003) it retrieves the stored number, not the
displayed number, and nor does it retrieve the display format information.
d. Word has no knowledge of how you want to display the number so outputs
its version of the internal representation.

In my view, ideally OLEDB, or whatever mechanism MS standardised on for
internal Office data communications, would let you retrieve either the
internal representation, or the displayed values, or both, but it doesn't.
And you could well ask "Why?" about that, too...

You can revert to the DDE connection method (check
Word|Tools|Options|General|"Confirm conversions at open", go through the
connection process again, and select DDE when offered), which will typically
retrieve the display version of the data, but it does have its own problems
and limitations.

I suspect that MS would have a chance to do this stuff better if it put a
bit more effort into ensuring that intra-Office data communications could
all be XML-based and used a very standard set of data types and information
about representation.

Just my 2c worth...

Peter Jamieson

> Thanks the solution was posted on this site. You have to format the field
> in
[quoted text clipped - 5 lines]
>> mail merge I get 10.03 represented as 10.0299999. How do I make word
>> represent the numbers I have in the excel file?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.