Extra digits in merge from Excel (Office 2003)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ann Scharpf - 13 Jun 2005 22:19 GMT

I am creating a mail merge using an Excel file and am having the problem that
other users have reported with extra digits after the decimal point.
However, some of the things I'm seeing do not jibe with what I've read in
these posts. I will use two numbers in the Excel file as examples throughout.

I should mention that I did refer to
http://www.gmayor.com/formatting_word_fields.htm and tried that method. Here
is what I am seeing:

In the regular Office 2003 mail merge, where Word is expected to format the
numbers, I am getting these two numbers:
67006.740000000005
33000

(One thing that is really strange about the first number is that, if I go to
the Excel spreadsheet and keep clicking the "Increase decimal" button, that
"5" digit at the end of the first number NEVER appears. I can go out to 100
significant digits and they are all zeroes following the "74".)

The first thing I tried was to add another column to the spreadsheet with
the function ROUND(C2,2). When I tried the merge again, merge dialog box
showed the column with my rounded numbers exactly matching the unrounded ones:
67006.740000000005
33000

So, then I came here and tried the gmayor.com instructions. When I changed
the Tools > Options to prompt me to confirm the conversion at open, I got
this:

Regular (Unrounded) column:
67006.740000000000000000000000000000000000 (34 zeroes)
33000.000000000000000000000000000000000000 (36 zeroes)

Rounded column:
67006.74
33000.00

It seems the only way to get the numbers that I am looking for is to:
1. Round the numbers, AND
2. Use the DDE conversion

Is there something that I am missing? I am trying to figure this out for a
bunch of people who are NOT advanced Word users, so I'd appreciate any
information you can give me to simplify the steps / explanation. Thanks for
any help you can give me.

Reply to this Message

Cindy M -WordMVP- - 14 Jun 2005 09:32 GMT

Hi =?Utf-8?B?QW5uIFNjaGFycGY=?=,

You don't mention whether you tried using formatting switches in the merge
fields?

If you don't want to do this for some reason, then indeed, you'll have no choice
but to go the route you described, or something similar (such as converting to
literal text in Excel).

> I am creating a mail merge using an Excel file and am having the problem that
> other users have reported with extra digits after the decimal point.
[quoted text clipped - 41 lines]
> information you can give me to simplify the steps / explanation. Thanks for
> any help you can give me.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message

Ann Scharpf - 14 Jun 2005 12:56 GMT

Yes, I was trying to avoid using switches because I know I will hear no end
of grief about how complicated Word is making things. (You know, the whole
"shoot the messenger" thing!)

From what I'd read in other posts/links, I thought that EITHER the rounding
OR the DDE would fix the problem. I did not think I would have to do BOTH.

> Hi =?Utf-8?B?QW5uIFNjaGFycGY=?=,
>
[quoted text clipped - 58 lines]
> This reply is posted in the Newsgroup; please post any follow question or reply
> in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 15 Jun 2005 11:14 GMT

Hi =?Utf-8?B?QW5uIFNjaGFycGY=?=,

> Yes, I was trying to avoid using switches because I know I will hear no end
> of grief about how complicated Word is making things. (You know, the whole
> "shoot the messenger" thing!)
>

Yeah, MS didn't make life any easier for the end-user with this new default
interface. Their hearts were in the right places, but they weren't listening
to the right people...

> From what I'd read in other posts/links, I thought that EITHER the rounding
> OR the DDE would fix the problem. I did not think I would have to do BOTH.

If you were using "normal numbers" with, say, just two decimal places, DDE
would work. The problem is that you want such a degree of accuracy, and with
computers there's always a bit of a discrepancy when it comes to lots of
decimal places.

Best thing for the user would probably be a button they can push in Excel to
"convert" the table to a mail merge data source (write the columns to a "temp"
sheet, and format them all as literal strings using the Data tools). Then it
won't matter which connection method they choose.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Reply to this Message