Zero in merge document when blank field in Excel

Posted on 2005-04-25
Medium Priority
Last Modified: 2008-01-09
Previous suggestion for this problem was : "Make sure the connection to the data source uses a method other than what it is now using. I think you want DDE"

How do we do this? Can't find an option in Word XP Mail Merge for DDE.

In Word 97/2000 there used to be an option to not print blank fields when data source is empty. Can;t find s#uch an option in Word XP.
Something as basic as merging an address does not seem as easy in Office XP?

Using Office XP Word & Excel.


Question by:rutlandict
  • 3
  • 3
  • 2
  • +1

Expert Comment

ID: 13858012
It seems to automatically "close up" the blank lines now, but I can't find the option to make it keep the blank lines when I have a blank field. I have been wondering about this myself but can't seem to find the setting, if it exists anymore.

Author Comment

ID: 13858719
I do want the blank lines closed up! - at present they are staying there and a zero is being put in. i.e. it is not automatically closing them up.

Expert Comment

ID: 13860005
The option to suppress blank lines is still there, it's just hidden, well hidden! Thankyou Microsoft!

By default it should be set but it appears that you have got it unset somehow. You should be able to reset it via code, or you can add the the old "Merge..." button to your Mail Merge toolbar and get the choice back.

Make sure you have the Mail Merge Toolbar visible
Go to Tools > Customize > Commands tab
Scroll down the Categories list and select "All Commands"
Scroll down the Commands list and find "MailMerge" (it's a fair way down but they are mostly in alphabetic order)
Click and Drag "MailMerge" to your Mail Merge Toolbar and the old icon will appear.
Close the customize dialog

Use the old button to get your option.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.


Expert Comment

ID: 13860697
Thank you Tony!!! And thanks to rutlandict for asking a question which has been bothering me (but not too much to make me ask here, as I haven't actually had to do a merge in awhile - I just noticed the blank line suppression option was missing...)

Author Comment

ID: 13864459
Under Tools-Customize-Commands - There isn't a command to drag called "Mailmerge"?

There is a column called categories (which has MailMerge in it but you can't drag that) and a column called commands (which can be dragged to the toolbar) but I can't see which button would allow you to suppress blank lines.

Here's the choice of commands under the category MailMerge:
Mail Merge Wizard
Show Mail Merge toolbar
Insert Merge Field
Insert Word Field
Show Fields
Highlight Fields
Data Form
Merge to document
Merge to printer
Error check
Find in Field
Data Form
Manage Fields
Add Record
Delete Record

What do you mean by "the old button"?

Expert Comment

ID: 13865181
Do not choose MailMerge from the category list (on the left) - choose "All Commands"
Then you will find "MailMerge" in the list of Commands (on the right)

When you have dragged this to the Toolbar you will have a button labelled "Merge..."
Click on this to do your merge (instead of the new "merge to new document" icon) and you will get the old dialog (from Word 2K) and will have the raio button options to suppress (or not) blank lines.

Author Comment

ID: 13868639
ok this solution means word does not print a blank line when the data field is empty but am still getting zeros.
These zeros are visible in Excel (if you choose to display zero values) due to a VLOOKUP i.e. they are not present in the cell VLOOKUP gets its data from but because there is no data there, the result achieved by VLOOKUP seems to be a zero by default.
This zero then ends up in the merged doc! (the merge field is the cell with the VLOOKUP formula in it).


Accepted Solution

TonyJollans earned 2000 total points
ID: 13869534
I'm sorry but there's no easy answer to this. Your cell in Excel has zero in it. The Mail Merge doesn't know or care how it got there.

Can you change your Excel formula to IF(VLOOKUP(whatever)="","",VLOOKUP(whatever))?

If not, it should be possible, though much more complex, to put some logic in Word fields to deal with it - but blank lines would not get suppressed (or not easily - for which read I don't know how to do it off the top of my head!). For eaxmple { IF { MERGEFIELD "VLOOKUPRESULT" } <> 0 { MERGEFIELD "VLOOKUPRESULT" } } (where the braces are field delimiters - not typed characters) would give spaces instead of zeroes.

Expert Comment

ID: 13871237
Make a copy of your Excel file.
Try saving the Excel file as a Text (.TXT) file.
Then save that Text file as an Excel file again.
Now try again to do your mail merge using your most recent Excel file.  

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question