Solved

# Mail Merge from Excel - Format Percent using Switches

Posted on 2010-01-04
2,436 Views
I am creating Statements from an Excel worksheet in Microsoft Word using a mail merge. I have a column of percentages in Excel formated as Percentage with 2 decimal places. When I merge the field into Word, 2% shows as .02. I tried playing with the field codes and created { ={MERGEFIELD Double_Points_Rate } *100 \# "0.0%"} (and various permutations thereof) but it either persists in displaying the .02, or displays 0% or gives me a syntax error.

How do I create the field (what steps do I take) so that my percentages look like 1% or 1.5%?
0
Question by:platdesign

LVL 11

Expert Comment

You can multiply by 100, then show % with escape character as \%
0

Author Comment

<!--[if gte mso 9]>   Normal  0      false  false  false                     MicrosoftInternetExplorer4 <![endif]--><!--[if gte mso 9]>  <![endif]--><!--[if gte mso 10]><![endif]--><!--[if supportFields]>= MERGEFIELD  Double_Points_Rate0.02 *100 \# #.#% <![endif]-->I've made progress on my own. I needed to select "MERGEFIELD Double_Points_Rate" and press ctrl+F9 to enclose it in an additional set of {} brackets. Now my code looks like this: {={MERGEFIELD Double_Points_Rate} *100 \# #.#% }

This is almost perfect. The only problem remaining is that a whole number (such as 2%) shows with the decimal which I want to be optional.

The output I currently have is: 1.5% or 2.%
The output I'm seeking is: 1.5% or 2%
<!--[if supportFields]><![endif]-->
0

Author Comment

Shoot - I don't know how to edit my comment to remove all that Word junk in there. Below is what the comment should look like.
*************************************

I've made progress on my own. I needed to select "MERGEFIELD Double_Points_Rate" and press ctrl+F9 to enclose it in an additional set of {} brackets. Now my code looks like this: {={MERGEFIELD Double_Points_Rate} *100 \# #.#% }

This is almost perfect. The only problem remaining is that a whole number (such as 2%) shows with the decimal which I want to be optional.

The output I currently have is: 1.5% or 2.%
The output I'm seeking is: 1.5% or 2%
0

LVL 76

Expert Comment

# means that the digit is optional. 0 is used for mandatory digits.

Try #0.0
0

Accepted Solution

Here is my final code which works great!

{={ MERGEFIELD Double_Points_Rate} *100 \# 0.0;}%

Data                              Result
0%                                   0.0%
1%                                   1.0%
1.5%                                1.5%
0

LVL 76

Expert Comment

Your solution appears to based on my comment, except that if the percentage is in double figures (e.g. 12.5%), you will get the wrong figure (2.5%).
0

Author Comment

Actually, I mis-typed when I entered my comment to which you responded. I already had it formatted with the 0 instead of the #. Regardless, your comment did not contribute to accomplishing my goal of properly multiplying the percentage by 100 which was the real question. You made a suggestion regarding the formatting which did not affect my outcome at all.
0

LVL 76

Expert Comment

I don't intend to raise an objection, but you already had the *100 in your question, so, unless you wanted to truncate the displayed value to be less than 10, your reported solution does not seem to improve on the one that I posted, which especially addresses your immediately prior comment (26177525).
0

Author Comment

To clarify: if you read the original question and my solution, you'll see that I was getting "syntax errors" and the solution was that I can't simply type {} brackets - I learned that I needed to highlight the text and press ctrl+f9 which was the point of confusion.

The rest was all fluff.
0

LVL 76

Expert Comment

OK, I missed the point. You quoted two wrong results and syntax errors. I was not addressing the syntax errors which could have been caused by a multiplicity of mistypings.

I also see that you are not a premium member, so points are more valuable to you than to us.

Don't forget to put the leading #  into the format string.
0

Author Comment

I actually don't know anything about the points. I don't care who gets them, but I do believe in honesty :)

I don't know what sort of member I am - I know I pay a fee for it, though.
0

LVL 76

Expert Comment

OK, platdesign, sorry to quibble. I just wanted to be clear. Historically, a few questioners have tried to ask numerous questions on the same set of available points, and have each time, tried to reclaim the points with their own, often unspecified, solution.

Premium members are flagged in the question. They seem to have unlimited points to spend. I think that they pay a particular level of subscription.
0

Author Comment

It looks like I fall into that category. I pay an annual subscription (not even sure how much) and I can ask all the questions I want. I looked up my email confirmation from the order and it says Premium so I guess that's me :)
0

LVL 76

Expert Comment

Another lack of observation on my part. It does say that in the question. Duh!
0

## Featured Post

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.