Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
John Richmond, Watford UK
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows only 3

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


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows only 3

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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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




  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Capt_Trips
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows only 3


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
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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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




--
Capt_Trips
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
John Richmond, Watford UK
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

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

"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 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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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





  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

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

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

Peter Jamieson
"John Richmond, Watford UK"
wrote in message
...
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

"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 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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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









  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
John Richmond, Watford UK
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

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

John

"Peter Jamieson" wrote:

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

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

Peter Jamieson
"John Richmond, Watford UK"
wrote in message
...
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

"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 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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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








  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
John Richmond, Watford UK
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

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


"Peter Jamieson" wrote:

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

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

Peter Jamieson
"John Richmond, Watford UK"
wrote in message
...
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

"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 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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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








  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

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

"John Richmond, Watford UK"
wrote in message
...
Peter,
I did that exactly and it came up with a syntax error.


"Peter Jamieson" wrote:

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

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

Peter Jamieson
"John Richmond, Watford UK"
wrote in message
...
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

"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
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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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










  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default ExcelWord Mail merge transfers 16 digits when Excel shows onl

See the relevant section of http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

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


John Richmond, Watford UK wrote:
Peter,
I did that exactly and it came up with a syntax error.


"Peter Jamieson" wrote:

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

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

Peter Jamieson
"John Richmond, Watford UK"
wrote in message
...
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

"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 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
"John Richmond, Watford UK" John Richmond, Watford
wrote in message
...
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
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Format Fractions in Mail Merge. John McGhie [MVP - Word and Word Macintosh] Mailmerge 4 March 22nd 06 09:55 AM
Mail Merge Word 2003 Excel 2003 database Gwendolyn Mailmerge 1 February 8th 06 03:59 PM
cannot mail merge from excel to word Alan Mailmerge 3 October 26th 05 05:03 AM
2003 Small Business - Mail Merge from Excel Sheet Duplication Oddi Los Mailmerge 1 May 4th 05 02:52 PM
mail merge with attachments AS Mailmerge 5 April 9th 05 09:49 AM


All times are GMT +1. The time now is 04:50 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"