1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Problem with retaining leading zero in mail merge.

Discussion in 'Business Applications' started by Leslie Scooter, May 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Leslie Scooter

    Leslie Scooter Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    11
    My Excel document of addresses includes a column labeled “Zip” which contains some zip codes with leading zeros.

    My Word document includes a merge field titled “Zip” in the appropriate location in the address block.

    The problem is with these leading zeros. I’ve tried both including and omitting an apostrophe before the zero in the Excel document; neither gives the desired result. One way gives just a zero and the other gives the Zip Code without the leading zero.

    Could you please let me know what I should do differently to make this work? Your help is very much appreciated! Thanks!

    Working in Window XP, Word 2002 and Excel 2002
     
  2. Leslie Scooter

    Leslie Scooter Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    11
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you tried forcing an Excel Number format of "X" number of characters to the left of the decimal point?
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Change the format to text within Excel. Also look into searching on the mergeformat switch within MS Word. I used this previously to retain the leading zeros for merged data.
     
  5. Leslie Scooter

    Leslie Scooter Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    11
    Thanks OBP and JIMR for your replys. (sorry not to respond more quickly but for some reason I didn't receive and email letting me know you had replied.)

    OBP - please tell me how to "force an Excel Number format of "X" number of characters to the left of the decimal point." I know you can increase and decrease decimals but that is to the right of the decimal point.

    JIMR - I did try changing the format to text in Excel but results were unchanged. Could you tell me more about "searching on the mergeformat switch within MS Word"?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Leslie, select "Custom" instead of Number and enter 000000
    ie as many zeroes as you need.
    See the attached sheet
     

    Attached Files:

  7. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    The problem exists in Word not excel. It drops the leading zeros for zip codes. I am about to head to lunch, but later on this afternoon I will look it up again for you and post a writeup on it.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Pasting the data in to word preserves the leading zeroes of the Custom format I have used, I am not sure a bout mail merge though.
     
  9. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Mergeformat was for character formatting. That must have been a different part of the lesson I was going to teach. Try this thread here for help.
     
  10. Leslie Scooter

    Leslie Scooter Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    11
    Yeah!! - finally... got it to work! Changing the MergeField Code as your article suggested to { Mergefield ZipCode \# "00000" } and formatting the zip to a "special - zip" number did the trick!

    Thanks so much for your help - I really appreciate you folk's expertise.
     
  11. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Not a problem, if you need further assistance feel free to post again.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/712259