Hello folks,

Mailmerge is new for me so I apologize for the elementary question. I have several fields in Excel that are blank. When these fields get mapped into the Word document, they are appearing as zero (0). If the value coming from Excel is blank, I want the field to display as blank on the Word document. Below is the latest example I have tried, but it does not work. Can someone help me with this? Thanks in advance.




{MERGEFIELD Petitioner_Att_Bar {IF {Mergefield Petitioner_Att_Bar} <> "" "{IF {Mergefield Petitioner_Att_Bar <> "0" "{Mergefield Petitioner_Att_Bar}" ""}" ""} \* MERGEFORMAT}

Hi Kingswood,

In addition to the blanks, do you also have true 0 values?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello Macropod, thank you very much for asking. There are no true 0 values. Each of the four fields either have values coming from Excel or they don't, and none of the four fields will have a 0 value. I would really appreciate if you know how to do this.

If you have the solution and if it's not too much trouble, I would really appreciate both solutions. At some point in the future, I may need to allow the 0. Thank you.

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.
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
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.

Handling cells that may be empty, as distinct from holding 0 values that you want to display requires a different approach. You can test the contents of a mergefield for text/nulls vs numbers by doing the following:
• select the field and Press Ctrl-F9 twice to enclose it in a pair of fields, thus { { «MyData» } };
• between the last pair of field braces press Ctrl-F9 twice to generate another pair of fields so that you end up with: { {SET Val «MyData» }{ { } } };
• before the new last pair of field braces press Ctrl-F9 to generate another field so that you end up with: { {SET Val «MyData» }{ { }{ } } };
• fill in around the new field braces so that you end up with: {QUOTE{SET Val «MyData»}{IF{REF Val}={=Val} {=Val \# $,0.00}}} (you can use any numeric format - see above);
• position the cursor anywhere in this field and press F9 to update it.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello Macropod, thank you very much! This worked for me. Being new to mail merge, I really appreciate your detail and step by step approach to this solution. Thank you again!

kingswood,

I hope you don't mind me jumping in with an alternative, and a question?

Mr. Edstein's response is very cool, he clearly is a very experienced OFFICE user, which I am not. I'm not very comfortable writing this type of procedure in WORD, I can't seem to remember the proper syntax and / or function keys to get there, however I'm a little more comfortable working in EXCEL. I do a lot of mail merges for creating golf scorecards and scoresheets where the handicaps sometimes need special formatting. I have taken to creating a new column in EXCEL using the text function to get what I want, and using it as the merge field.

The solution I use is to create the additional field "Petitioner_Att_Bar_Text" in my EXCEL table using the formula

= TEXT(Petitioner_Att_Bar,"$###,##0.00;-$###,##0.00;")

and using that field in the mail merge.

Mr. Edstein, is there a downside to attacking the problem in this manner?

Thanks for allowing me to participate!

Kevin

Hi Kevin,

The disadvantage that immediately springs to mind is the fact you need to modify the datasource - adding an extra column for every existing column that has the problem 0s in it. In some organisations, that would be frowned upon - or simply not possible (e.g. one generally can't go around modifying corporate databases).
Fair enough, but that's why we have word-processors, etc., where one can store notes (which in this case require little more than a quick copy/paste) for things we don't use very often. Doubtless you have something such already for Excel...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you Sir. I appreciate the response!

Kevin

Another good reason for using field switches is that their use makes the formatting in the Word document independent of the formatting in the Excel workbook. For example, you might have numbers without thousands separators in Excel, but want them to show in the Word document; or you might want to differentiate between +ve and -ve values by colour - something you can't do via any kind of formatting at the Excel end. With dates, you might want to have day-of-week, date & month only, whereas Excel hold date, month & year, or you might want the date to appear in one format at one location and in another format somewhere else. All of this can be achieve via field switches in Word, with no changes to the datasource.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]