Merge field formatted as % retains decimal

[Ellen]

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

 

[Graham Mayor]

You have to provide the formatting in Word so
{=({Mergefield fieldname} * 100)\# "0.00%"}
See also http://www.gmayor.com/formatting_word_fields.htm
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Ellen]

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

 

[Tony]

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

 

[Graham Mayor]

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.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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%" }


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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>