Excel>Word Mail merge transfers 16 digits when Excel shows only 3

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Richmond, Watford UK - 18 Apr 2006 13:51 GMT

In producing reports by mailmerge from Excel into Word, an Excel cell holding
a calculated value formatted to display as 3.45 will be shown as
3.449993782999200000. Even cells carefully formatted to integers e.g 3 will
often transfer to 3.0000000000000001. Percentages such as 78% get
transferred as 0.798745300322220 Only if the values have been typed in will
the mail merge show the correct number.

This did not happen with previous versions of Word and Excel, where the
number was transferred as formatted in Excel. Is there any way of putting
this right?

Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

Peter Jamieson - 18 Apr 2006 16:56 GMT

The simplest way to revert to previous behaviour is generally to check
Word|Tools|Options|General|Confirm conversion at Open, re-connect to your
Excel data source and choose the DDE option when offered. I suspect you must
have been using that in Word XP as you would probably be having the same
problems.

Alternatively, you can use "formatting switches" in the fields, e.g. use
Alt-F9 to show

{ MERGEFIELD mynumber \*Mergeformat }

and change it to

{ MERGEFIELD mynumber \#0.00 }

(you can leave the Mergeformat in there if you want)

The formats are documented in Word Help.

Peter Jamieson

> In producing reports by mailmerge from Excel into Word, an Excel cell
> holding
[quoted text clipped - 12 lines]
>
> Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

Capt_Trips - 18 Apr 2006 17:28 GMT

Thanks Peter, wasnt my post but solved my issue from earlier!

Peter Jamieson Wrote:

> The simplest way to revert to previous behaviour is generally to check
> Word|Tools|Options|General|Confirm conversion at Open, re-connect to
[quoted text clipped - 39 lines]
>
> Versions in use are Word 2002 SP3 and Excel 2002 SP3

Signature

Capt_Trips

Reply to this Message

John Richmond, Watford UK - 18 Apr 2006 18:52 GMT

Thanks.
It worked except for % where I had to be content with an output with a
decimal point viz: .79% This came as a result of using a format of \#.00%.
The % data itself is, of course, all stored as numbers < 1 but displayed in
Excel as a %ge. I'm not changing that so will be happy with what I've got.

Many thanks

John Richmond

> The simplest way to revert to previous behaviour is generally to check
> Word|Tools|Options|General|Confirm conversion at Open, re-connect to your
[quoted text clipped - 32 lines]
> >
> > Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

Peter Jamieson - 18 Apr 2006 19:07 GMT

You cn get the percentage using a nested mergefield, e.g.

{ ={ MERGEFIELD mypercentage }*100 \#0.0 }

Peter Jamieson

> Thanks.
> It worked except for % where I had to be content with an output with a
[quoted text clipped - 47 lines]
>> >
>> > Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

John Richmond, Watford UK - 18 Apr 2006 22:09 GMT

Thanks Peter, I'll try it and let you know how I got on

John

> You cn get the percentage using a nested mergefield, e.g.
>
[quoted text clipped - 52 lines]
> >> >
> >> > Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

John Richmond, Watford UK - 25 Apr 2006 23:33 GMT

Peter,
I did that exactly and it came up with a syntax error.

> You cn get the percentage using a nested mergefield, e.g.
>
[quoted text clipped - 52 lines]
> >> >
> >> > Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

Peter Jamieson - 26 Apr 2006 01:07 GMT

Both sets of {} need to be the sort you insert using ctrl-F9. Everything
else is plain text you can type in the usual way. If you just used plain
text braces around the mergefield you will get a syntax error.

Peter Jamieson

> Peter,
> I did that exactly and it came up with a syntax error.
[quoted text clipped - 64 lines]
>> >> >
>> >> > Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message

Graham Mayor - 26 Apr 2006 06:00 GMT

See the relevant section of 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
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Peter,
> I did that exactly and it came up with a syntax error.
[quoted text clipped - 55 lines]
>>>>>
>>>>> Versions in use are Word 2002 SP3 and Excel 2002 SP3

Reply to this Message