Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3338
  • Last Modified:

Mail Merge from Excel - Format Percent using Switches

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
platdesign
Asked:
platdesign
  • 7
  • 6
1 Solution
 
ashraf882Commented:
You can multiply by 100, then show % with escape character as \%
0
 
platdesignAuthor Commented:
<!--[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
 
platdesignAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Try #0.0
0
 
platdesignAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
platdesignAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
platdesignAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
platdesignAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
platdesignAuthor Commented:
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
 
GrahamSkanRetiredCommented:
Another lack of observation on my part. It does say that in the question. Duh!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now