A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Number from xcel 03 does not appear exactly as is when merged to w



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2010, 06:25 PM posted to microsoft.public.word.mailmerge.fields
Box Guy
external usenet poster
 
Posts: 1
Default Number from xcel 03 does not appear exactly as is when merged to w

I have numbers, expressed as 16ths (example 21 9/16) in an xcel file that I
want to show, exactly as is- in 16ths, in a merge doc. However the merge doc
shows decimal. For the example above, it shows 21.5625. Note that the cell
in xcel with the 16ths number is populated copying another cell that starts
as a decimal.
  #2  
Old March 4th, 2010, 09:00 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Number from xcel 03 does not appear exactly as is when mergedto w

You have a number of options, including:
a. use DDE to connect to your data source. (In Word, check
Tools-Options-General-Confirm conversions on open, then reconnect to
the data source and choose the DDE option when it is offered. That will
only work for the first sheet in the workbook, and is not a recomended
approach long term
b. Select your data, Edit-Copy in Excel, and Edit-Paste into a Word
document which you then use as the data source. Less likely to work if
you have more columns than the maximum for a Word table, etc. etc.
c. you can use some nested fields in the Word document to turn ".0625"
back to 1/16 etc. For example, if your column is called "f", you could
try something like:

{ =int({ MERGEFIELD f }) } { =16*mod({ MERGEFIELD f },1 } \#"0'/16';;" }

(That would always give you 1/16, 2/16, 3/16, etc., but blank for 0/16).

ALl the {} have to be the special "field braces" that you can insert
using ctrl-F9.

If you need

1/16, 1/8, 3/16, 1/4 etc. you would probably be better off doing
something like this:

At the beginning of your document, put

{ SET F00 "" }{ SET F06 "1/16" }{ SET F13 "1/8" }{ SET F19 "3/16" } etc.
etc. up to { SET F94 "15/16" }

- where "06" stands for "0625" etc.

Notice that 2 digits are enough to distinguish between the different
values but that you need to round up, e.g. "19"instead of "18" for 1875

Then use something like

{ =int({ MERGEFIELD f }) } { REF "F{ =100*(MOD({ MERGEFIELD f },1) \#00 }" }


Peter Jamieson

http://tips.pjmsn.me.uk

On 04/03/2010 17:25, Box Guy wrote:
I have numbers, expressed as 16ths (example 21 9/16) in an xcel file that I
want to show, exactly as is- in 16ths, in a merge doc. However the merge doc
shows decimal. For the example above, it shows 21.5625. Note that the cell
in xcel with the 16ths number is populated copying another cell that starts
as a decimal.

  #3  
Old March 5th, 2010, 09:36 PM posted to microsoft.public.word.mailmerge.fields
Box Guy[_2_]
external usenet poster
 
Posts: 1
Default Number from xcel 03 does not appear exactly as is when merged

Thanks, I think option c is the way to go and I always want the # expressed
in 16th's (not 1/4 for example) except when 0. When you type "...you could
try something like:...". Where do you "try it". In other words where do I
type all that stuff that you show in the brackets? In each cell I want
expressed as a 1/16th?

"Peter Jamieson" wrote:

You have a number of options, including:
a. use DDE to connect to your data source. (In Word, check
Tools-Options-General-Confirm conversions on open, then reconnect to
the data source and choose the DDE option when it is offered. That will
only work for the first sheet in the workbook, and is not a recomended
approach long term
b. Select your data, Edit-Copy in Excel, and Edit-Paste into a Word
document which you then use as the data source. Less likely to work if
you have more columns than the maximum for a Word table, etc. etc.
c. you can use some nested fields in the Word document to turn ".0625"
back to 1/16 etc. For example, if your column is called "f", you could
try something like:

{ =int({ MERGEFIELD f }) } { =16*mod({ MERGEFIELD f },1 } \#"0'/16';;" }

(That would always give you 1/16, 2/16, 3/16, etc., but blank for 0/16).

ALl the {} have to be the special "field braces" that you can insert
using ctrl-F9.

If you need

1/16, 1/8, 3/16, 1/4 etc. you would probably be better off doing
something like this:

At the beginning of your document, put

{ SET F00 "" }{ SET F06 "1/16" }{ SET F13 "1/8" }{ SET F19 "3/16" } etc.
etc. up to { SET F94 "15/16" }

- where "06" stands for "0625" etc.

Notice that 2 digits are enough to distinguish between the different
values but that you need to round up, e.g. "19"instead of "18" for 1875

Then use something like

{ =int({ MERGEFIELD f }) } { REF "F{ =100*(MOD({ MERGEFIELD f },1) \#00 }" }


Peter Jamieson

http://tips.pjmsn.me.uk

On 04/03/2010 17:25, Box Guy wrote:
I have numbers, expressed as 16ths (example 21 9/16) in an xcel file that I
want to show, exactly as is- in 16ths, in a merge doc. However the merge doc
shows decimal. For the example above, it shows 21.5625. Note that the cell
in xcel with the 16ths number is populated copying another cell that starts
as a decimal.

.

  #4  
Old March 5th, 2010, 09:59 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Number from xcel 03 does not appear exactly as is when merged

Where do you "try it". In other words where do I
type all that stuff that you show in the brackets?


In the Word document at the point where you need to insert the information.

I think once you've tried that once, things may seem a little clearer,
but...

In each cell I want
expressed as a 1/16th?


If you mean "in each cell in Excel" then no, it's just in Word. But if
you are merging into a table in Word, yes, you might need to replicate
the field codes. It's likely a copy-paste job with some changes to the
field names.


Peter Jamieson

http://tips.pjmsn.me.uk

On 05/03/2010 20:36, Box Guy wrote:
Thanks, I think option c is the way to go and I always want the # expressed
in 16th's (not 1/4 for example) except when 0. When you type "...you could
try something like:...". Where do you "try it". In other words where do I
type all that stuff that you show in the brackets? In each cell I want
expressed as a 1/16th?

"Peter Jamieson" wrote:

You have a number of options, including:
a. use DDE to connect to your data source. (In Word, check
Tools-Options-General-Confirm conversions on open, then reconnect to
the data source and choose the DDE option when it is offered. That will
only work for the first sheet in the workbook, and is not a recomended
approach long term
b. Select your data, Edit-Copy in Excel, and Edit-Paste into a Word
document which you then use as the data source. Less likely to work if
you have more columns than the maximum for a Word table, etc. etc.
c. you can use some nested fields in the Word document to turn ".0625"
back to 1/16 etc. For example, if your column is called "f", you could
try something like:

{ =int({ MERGEFIELD f }) } { =16*mod({ MERGEFIELD f },1 } \#"0'/16';;" }

(That would always give you 1/16, 2/16, 3/16, etc., but blank for 0/16).

ALl the {} have to be the special "field braces" that you can insert
using ctrl-F9.

If you need

1/16, 1/8, 3/16, 1/4 etc. you would probably be better off doing
something like this:

At the beginning of your document, put

{ SET F00 "" }{ SET F06 "1/16" }{ SET F13 "1/8" }{ SET F19 "3/16" } etc.
etc. up to { SET F94 "15/16" }

- where "06" stands for "0625" etc.

Notice that 2 digits are enough to distinguish between the different
values but that you need to round up, e.g. "19"instead of "18" for 1875

Then use something like

{ =int({ MERGEFIELD f }) } { REF "F{ =100*(MOD({ MERGEFIELD f },1) \#00 }" }


Peter Jamieson

http://tips.pjmsn.me.uk

On 04/03/2010 17:25, Box Guy wrote:
I have numbers, expressed as 16ths (example 21 9/16) in an xcel file that I
want to show, exactly as is- in 16ths, in a merge doc. However the merge doc
shows decimal. For the example above, it shows 21.5625. Note that the cell
in xcel with the 16ths number is populated copying another cell that starts
as a decimal.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.