Zero in merge document when blank field in Excel

Posted on 2005-04-25
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
    LVL 8

    Expert Comment

    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

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

    Expert Comment

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

    Expert Comment

    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

    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"?
    LVL 6

    Expert Comment

    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

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

    LVL 6

    Accepted Solution

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

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now