Merge field formatted as % retains decimal

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ellen - 27 Aug 2004 15:52 GMT

Office 2003 on XP.

Mail merge w/ Excel source, Word destination

My source spreadsheet has several columns of data that
are percentages. Raw data: .86926563 The data is
formatted as a percentage. Example: 86.93%

When I merge the data into a Word document and use a
number switch, it retains the decimal from the raw data.
Example: Raw data .86926563 and {field \# "#0.00%"}
results in .87% instead of expected 86.93%

I've tried removing the formatting on the spreadsheet.
Doesn't change anything.

Am I doing something wrong?

If this is a bug, is there a workaround? Things I've
considered:

It's a big spreadsheet and we use the spreadsheet for
other purposes, so I don't want to have to insert an
extra column multiplied by 100 for every percentage
column.

Is there any way to combine a formula field and a merge
field and multiply by 100 there?

Thanks.
Ellen

Reply to this Message

Graham Mayor - 27 Aug 2004 16:00 GMT

You have to provide the formatting in Word so
{=({Mergefield fieldname} * 100)\# "0.00%"}
See also 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
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Office 2003 on XP.
>
[quoted text clipped - 27 lines]
> Thanks.
> Ellen

Reply to this Message

Ellen - 27 Aug 2004 16:16 GMT

Hi Graham,

Yes, I was providing the formatting in Word using the
number switch. Guess I didn't make that clear. This looks
like the formula and syntax I was looking for. Thanks
very much!

Ellen

>-----Original Message-----
>You have to provide the formatting in Word so
[quoted text clipped - 33 lines]
>
>.

Reply to this Message

Tony - 23 Nov 2004 23:29 GMT

We have the same problem. Our field is named Exam_1 and we followed
Graham's link, but we still aren't able to show 86.44%. We start off with
84.44398798279 and when we add the formatting (exactly as this):

{={<<Exam_1>>}*100\#"0%"}

But all we saw was {={<<84.44398798279>>}*100\#"0%"}

Any help would be greatly appreciated. Could you write the line with Exam_1
being the field?

Thanks for your help,
Tony

> Hi Graham,
>
[quoted text clipped - 42 lines]
> >
> >.

Reply to this Message

Graham Mayor - 24 Nov 2004 07:53 GMT

If the result of the field comes in from the data source as 84.44398798279
then you don't need to multiply it by 100.
What you appear to be doing is inserting the string as text rather than
fields. Use CTRL+F9 to set the field boundaries {} and the required syntax
for this particular example would be

{ ={ Mergefield Exam_1 } \# "0%" }

Select the field right click and toggle the display then press F9 to update.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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

> We have the same problem. Our field is named Exam_1 and we followed
> Graham's link, but we still aren't able to show 86.44%. We start off
[quoted text clipped - 65 lines]
>>>
>>> .

Reply to this Message

Graham Mayor - 24 Nov 2004 07:58 GMT

You've got me at it now :(

Forget the last (though that would work) - it should simply be
{ Mergefield Exam_1 \# "0%" }

or if you want to ensure that 84.51% does not roundup to 86% then

{ =INT{ Mergefield Exam_1 } \# "0%" }

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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

> We have the same problem. Our field is named Exam_1 and we followed
> Graham's link, but we still aren't able to show 86.44%. We start off
[quoted text clipped - 65 lines]
>>>
>>> .

Reply to this Message

Graham Mayor - 24 Nov 2004 11:10 GMT

I'm having one of those senior moment days - that should have read 'round up
to 85%' but then you guessed that :)

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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

> You've got me at it now :(
>
[quoted text clipped - 74 lines]
>>>>
>>>> .

Reply to this Message