Hi folks,

I don't know much about mail merging, so I have a question.  I have most things working on my merge, but need help with a couple of minor items (I hope).  I have an Excel 2010 spreadsheet that we use for merging to a Word 2010 document.  It has various fields such as name, address, city, etc.  It also has a field for a dollar amount formatted to two decimal places and showing the $ sign in Excel (ex. $67,000.00), but when it gets merged into the Word document, it displays as 67000.  It doesn't pull the $ nor the decimal point and cents.

I also have the same thing with a percentage (Ex. 6.00%), displays in the Word document as .006. 

I think I have everything else working, but can't figure out how to format these items in the Word document to display properly.  Any advice?

Mike

Hi Mike,

Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
 
Note: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values
Note: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
 
If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.
 
Mailmerge Percentage Formatting
To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
• select your mergefield, which will look something like «Percent»;
• press Ctrl-F9 to wrap another field around it, thus { «Percent» };
• edit the field so that you get {=«Percent»*100 \# 0.00%};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge. 
Note: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

Paul,

Thanks for the detailed response, I appreciate your time.  I will give this a try.

Update:  It worked perfectly Paul, thanks for the tips. 

Mike

I have tried this and have found that it makes the preview look right but the long decimals continue to be present when the actual merge takes place in print or in edit merged document.

Are there any fixes for this?

zeddock

Cross-posted at: http://www.msofficeforums.com/word/8272-mail-merge-number-2-decimal-places.html#post21662

For cross-posting etiquette, please read: http://www.excelguru.ca/node/7

Unless you forgot to save the mailmerge main document with the switches in place, I cannot imagine how you could get different final results than what the preview shows.

Paul, I will only post here an apology.  Since I was searching for help on this, I did not realize the two forums were connected.  Sorry. This response forum is of great use to many so I would not want to cheapen it by disregarding the etiquette. Again, Sorry.

The document was saved.  I cannot figure it out.  The only way I was able to fix it was to set the column as TEXT...  (in Google Docs before downloading it as an excel format)... not numbers.  But I do not think this is a reasonable fix.

If I look at the spreadsheet and see a format of 2 decimal places, I should get that.

If you want for me to take specific steps to make sure it works right, please list them here?  I would love to put this to bed.

zeddock                   

Hi zeddock

The forums aren't linked. The only issue is that, if people don't know you've posted in multiple forums, you'll end up with different people needlessly re-inventing the solution wheel.

Since the problem seems to have something to do with the way you're compiling the source data, you might try using a formula to foce the numeric conversion):
• select your mergefield, which will look something like «Data»;
• press Ctrl-F9 to wrap another field around it, thus { «Data» };
• edit the field so that you get {=«Data» \# ",0.00"};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Thanx Paul.

I can't do this right now as the computer is elsewhere, but this leads to another question perhaps;

I make the change above and then save the word file, correct?

Now, when I generate a new, updated spreadsheet, I open up the word document and use the step-by-step mail merge.

On the right side of the screen I get to the step where I need to give a different list (can't remember the exact wording).

I select the new spreadsheet and the table to which I will connect, but from what I recollect I get an error that complains that the field is no longer valid, so now I have to rebuild it with the switch codes.  This is a pain but I could handle it.... however, I have someone else trying to do this on a very regular basis and it just is not sustainable.

Do you think I am missing a step or does it make sense that linking to a new data source creates a mis-match with the augmented field?

Thanx again.

zeddock

Hi zeddock,

You're going about this the wrong way - and maybe that's what's behind your problems.

You should design your mailmerge to always use the same data source. That way, you don't need to re-create things each time. At most, this should mean creating a 'generic' workbook (eg named 'MMData' by copying & re-naming an existing workbook) and, each time you want to do a mailmerge with the new data, simply copy the new workbook over the old one. Word won't be any the wiser and, when you open the mailmerge main document, everything should be ready to go.

Hi,

I've followed the instructions here for how to add a numeric picture switch to a merge field. But this does not solve my problem, which is a bit different than the original question. I hope that someone will be willing to help me solve my problem.

I want to merge dollar values from Excel into a Word document, and where the value in Excel has a zero value after the decimal point, I want to omit the decimal places. Where the value in Excel is greater than zero, I want the value to appear in the merge.

For example: Bob gave $200.00. I want his letter to reflect that he gave $200. Jane gave $200.50. I want her letter to reflect that she gave $200.50.

I've tried the solution from www.gmayor.com and I could not get it to work.

Also, in your examples the field name that appears after MERGEFIELD does not appear in quotes. But in my document, the field name appears in quotes. Like this:

{MERGEFIELD "CnGf_Amount"}

Does that matter?

This puzzle is driving me nuts. Any help will be much appreciated.

David

• Edited by dmjaeger Tuesday, November 08, 2011 7:10 PM

Hi David,

You could try a field constructed as:
{QUOTE{SET Val {MERGEFIELD "CnGf_Amount"}}{IF{Val}={=INT(Val)} {Val \# $,0} {Val \# $,0.00}}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

Hey Paul, It worked! Thanks!

Two questions:

ONE: But after I run it once the formula in my merge document changes from the language that I typed out (the string above) to "$2,000." If I press SHIFT+F9, it reads: {QUOTE $2,000}. I notice that if I go to each piece of the merge field formula and press SHIFT+F9, it will unpack it and eventually the formula appears. Is there any way to not have this happen every time I run this merge? That "packing" of the formula is confusing. It'd be great if it would just read the way I expect it to when i press SHIFT+F9.

TWO: I have about 12+ documents to edit with this new formula. Is there a way to copy and paste it into those documents? I'll try and see what happens, but I'd love to see if you have a trick for duplicating the complex formula.

Thanks again. This is great to see.

David

• Edited by dmjaeger Tuesday, November 29, 2011 7:57 PM

Hi David,

The simplest way to expand the entire field code set in a document is to press Alt-F9; otherwise, you need to select a whole field (not just position the cursor in it or select part of it) before pressing Shift-F9.

As for copying & pasting, you can indeed do that - simply select the entire field, copy it, then paste it wherever else you want it to appear.

Paul!

This was so super helpful to me! I've been able to take my decimal values and make them a percentage. However I've had a slight problem and although I've looked through some of the other threads I haven't been able to find a solution and thought you might be able to help.

In my mail merge I am populating a table with 6 percentages for each person on my team based on our 6 goals. The problem I am having is that some of the values from my data source are N/A, and I actually need them to be reported in the merge as N/A. (because that person is not responsible for that goal). This wasn't a problem before I wrapped the other field around the mergefield. At first it was displaying "N/A" now it is displaying "!Undefined Bookmark, N". Is there something I could add to my fields that would check the data source and if it said N/A put N/A but if it's a number convert it to a percentage?

Thank you all for your help!

Sincerely,

Mike

If it's really "N/A" as inserted by a user and not the #N/A that Excel can generate (e.g. using =NA() ) then you can test for that specific value first, or for anything starting with N, e.g. like this

{QUOTE{SET Val {MERGEFIELD "CnGf_Amount"}}{IF "{ REF Val \*Uppercase }" = "N*" "{ REF Val }" "{IF{Val}={=INT(Val)} {Val \# $,0} {Val \# $,0.00}}}" }" }

(You can eliminate some of the "" etc. inthere if you prefer).

However, mixing text and numeric values in an Excel column can cause problems. For example, if the first 8 cells in the column all contain numbers (not "N/A") then your "N/A" values will probably come through as zeroes.

Hi There,

I am having a similar problem with negative numbers. I have done the steps below. My problem is that I have a couple of fields that I am trying to display the difference in sales target compared to actual sales and sometimes the negative number displays correctly and sometimes it does not. Please help!

Displays "!Syntax Error, -", Should be -$23,103 - {IF {MERGEFIELD Total_NB_Premium_away_from_Plan}= "Not Applicable" "Not Applicable" {IF{MERGEFIELD Total_NB_Premium_away_from_Plan}="TBD" "TBD" {={MERGEFIELD Total_NB_Premium_away_from_Plan}\#$,0.00}}

I even tried to include "\#,0.00;(#,0.00)"  and it did not change the results

Displays - $-164,209.00 - {IF{MERGEFIELD Total_Premium_of_Team_Sales_Target}= "Not Applicable" "Not Applicable" {IF{MERGEFIELD Total_Premium_of_Team_Sales_Target}="TBD" "TBD" {={MERGEFIELD Total_Premium_of_Team_Sales_Target }\#$,0.00}}

If you insert the fields

{ MERGEFIELD Total_NB_Premium_away_from_Plan }
and
{ MERGEFIELD Total_Premium_of_Team_Sales_Target }
directly in your mail merge main document, outside an IF field, not nested in an { = } field, with no formatting switches, what results do you see?

Word will only apply a numeric format to things that it decides are numeric (broadly speaking, that includes numeric and currency amounts that it can recognise). It will leave anything else unformatted. So if you have something that looks a bit like a number like "$-164,209.00", the chances are that Word will leave it exactly as it is.

When Word does not recognise something as being a number or a valid numeric expression, it will generally display one error or another when you try to put it in an { = } field. So, e.g. 

{ =$-164,209.00 } would give you the syntax error that you are seeing. (Assuming "$" is set up as the currency symbol in your COntrol Panel's regional options. If the regional currency symbol was , you would see a different syntax error.)

The problem in this case probably originates in something that has happened in Excel. Normally, things that are numeric/currency in Excel come through as things that Word treats as numeric. But if for example, someone has entered a number formatted as text in a column that has texts rather than numbers above it, Word may receive something that it does not think is numeric. You may have to fix your sheet before Word will "see" the numbers correctly.

When you have got to the point where WOrd sees the numbers correctly, you should be able to use

{ MERGEFIELD Total_NB_Premium_away_from_Plan} \#$,0.00 }

(and the negative numbering formats if you want) without nesting the { MERGEFIELD } inside an { = }

I am having issues with the percentage formatting.  My number is still coming out as .82% after changing the merge fields.  Any help would be greatly appreciated.

Thanks

Hello,

This answer is fantastic and really helped me out.

I'm just a little stuck on the percentage as it seems it doesn't recognize the *100 as my answer is still displaying at 0.5 instead of 5%. I'm thinking the problem is that I'm incorrectly adding the formula. Would you be able to help me with that? I don't have much experience using formulas in Word. Does it matter that my data from excel is already formatted as a percentage?

This is what my field currently looks like:  {MERGEFIELD Rabais2_}

I've keyed in :

{=MERGEFIELD Rabais2_*100\#0.0%}  and I get a Syntax error. I keyed in by doing ALT 174 and 175.

Your help would be greatly appreciated.

Carolyn

Starting from where you are I suggest you...

a. delete the MERGEFIELD Rabais2_ (it's just text)
b. leave the insertion point immediately after the "=" sign
c. re-insert the field code from the appropriate dropdown (depends on your version of Word). Or, press ctrl-F9, which should insert a pair of the sepcial field code braces {  }, then type

MERGEFIELD Rabais2_ 

between the {   }

By way of explanation, Word can display a field in various different ways -

 - as  { MERGEFIELD Rabais2_ } . In this case, the pair of  {  } are the special field code braces that you can insert in Windows Word using ctrl-F9 - you can't just type them using the keyboard characters { and }
 - as Rabais2_ . This is just a different way that Word presents { MERGEFIELD Rabais2_ }. But if you try to enter  Rabais2_ yourself, Word does not actually recognise it as a field code.
 - as the field result, when you preview the data.

Woo-hoo! Thank you. It took me a few tries but I got it. :)

Is it normal that it doesn't display as a field anymore but as the answer? If I click on the field and press shift F9 I can visualize it all; however, even there the field is displayed as the answer.

In Windows Word, Alt-F9 once or twice (it depends on what's showing when you start) should display all the *field codes*. As a general rule, the main exception is that displaying/not displaying "hidden tex" may have an impact on what you actually see.