Word is adding zeros in front of numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nat - 19 Jul 2005 21:11 GMT

I have Office 2003 and am attempting to merge MSExcel data into a Word
document. I have been working through the problems associated with formats,
however, there is one that I can't seem to resolve. Sometimes the merged
numerics that appear in MSWord have a zero in front of the number, e.g. 056
or -078. This only occurs when the actual number in the Excel spreadsheet is
less than 100 and also only seems to occur when the data in Excel is the
result of a formula, e.g. "=A3+B3". When data is merely entered as a numeric
value, e.g. 57, it appears as 57 in Word, not 057. Suggestions would be
helpful. Thank you

Reply to this Message

Anne Troy - 20 Jul 2005 03:37 GMT

This may help, Nat:
http://www.officearticles.com/word/merge_field_formatting_in_microsoft_word.htm
*******************
~Anne Troy

www.OfficeArticles.com

> I have Office 2003 and am attempting to merge MSExcel data into a Word
> document. I have been working through the problems associated with formats,
[quoted text clipped - 5 lines]
> value, e.g. 57, it appears as 57 in Word, not 057. Suggestions would be
> helpful. Thank you

Reply to this Message

Nat - 20 Jul 2005 12:01 GMT

Thank you for the response Anne, but it doesn't quite get at my problem. My
problem is that the merge feature is ADDING a zero in fron of some numbers -
not dropping them. I think that I could include a switch that limits the
number of digits to the LEFT of a decimal point - thus eliminating the zero,
but this will not allow that merged item to include three digit numbers when
needed. What I need is a solution to get RID of a leading zero - not add
leading zeros. Thanks

> This may help, Nat:
> http://www.officearticles.com/word/merge_field_formatting_in_microsoft_word.htm
[quoted text clipped - 16 lines]
> > value, e.g. 57, it appears as 57 in Word, not 057. Suggestions would be
> > helpful. Thank you

Reply to this Message

Anne Troy - 20 Jul 2005 14:49 GMT

Okay, Nat. So what does your merge field look like when you view it? Also,
what's the cell format in Excel?
*******************
~Anne Troy

www.OfficeArticles.com

> Thank you for the response Anne, but it doesn't quite get at my problem. My
> problem is that the merge feature is ADDING a zero in fron of some numbers -
[quoted text clipped - 5 lines]
>
> > This may help, Nat:

http://www.officearticles.com/word/merge_field_formatting_in_microsoft_word.htm

> > *******************
> > ~Anne Troy
[quoted text clipped - 14 lines]
> > > value, e.g. 57, it appears as 57 in Word, not 057. Suggestions would be
> > > helpful. Thank you

Reply to this Message

Nat - 21 Jul 2005 14:30 GMT

Hi Anne.
My goal is for the merged data to appear as numbers that look something like
2,300, 75, etc. The dilemma is that, thus far, I have not been able to
achieve that outcome using switch codes - it seems that I must sacrifice one
of three things. First, when using a switch code "\#0" I can obtain the
desired effect, but no commas are present. If I use "\#,#" then mergefields
for which the data source reports a zero are ommitted in the merged document.
If I use "\#0 #,#" then no commas appear. If I modify slightly by "\#0#,#" I
get my zeros and commas, but data in the MSExcel document that are the
product of a formula, e.g. "=sum(A1:A4)" AND are less than three digits, e.g.
45, are presented as "045."

Thus far, I have not been able to find the right combination of switches
that will give me: commas, viewable data-source zero-value-entries, AND
properly formatted merge results, e.g. 45, that is the product of a formula
in the source document. I simply don't know what other switches or solutions
might be available. Thank you for any assistance or solution you might be
able to provide.

Nat

If I want the commas then I have to accept numbers like "075."
Alternatively, I can get the number to look like "75" but then I can't get
the commas. And yet another alternative, I can indeed get the commas and the
two digit numbers looking right, but I will lose other merged values for
which a zero is entered in the spreadsheet.

I am using an MSExcel file as the source document and merging to an MSWrod
document. The situation occurs for a couple of cells, all with the same
structure. First, the MSExcel content is the result of a formula, e.g.
"=sum(s3:s9)."

Second, result of the formula is a two digit number, e.g. 59. When the
number is three digits there is no problem. In the MSWord document, an
example of a problematic merge field (when viewed with the F9 toggle) is {
MERGEFIELD "Total_Revenue" }. The result looks something like "059." I have
tried a couple of switches in hopes of getting the appearance I want but can
never get every thing I want. For example, { MERGEFIELD "Total_Revenue" \#0
} gives "59," but when I try to include a switch that will format using
commas in larger numbers { MERGEFIELD "Total_Revenue" \#0 #,#} it goes back
to "059."

solutions but I have not been able to get the

> Okay, Nat. So what does your merge field look like when you view it? Also,
> what's the cell format in Excel?
[quoted text clipped - 41 lines]
> be
> > > > helpful. Thank you

Reply to this Message

Peter Jamieson - 21 Jul 2005 17:19 GMT

Did you try \#,0
?

Peter Jamieson

> Hi Anne.
> My goal is for the merged data to appear as numbers that look something
[quoted text clipped - 106 lines]
>> be
>> > > > helpful. Thank you

Reply to this Message

Nat - 22 Jul 2005 13:12 GMT

That's the answer! Thank you Peter!

> Did you try \#,0
> ?
[quoted text clipped - 110 lines]
> >> be
> >> > > > helpful. Thank you

Reply to this Message