Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zip Codes drop starting zeroes when merged (2003)

    I'm using an Excel spreadsheet for an address list. The Zip Code field is formatted as Special-Zip Code and displays properly with leading zeroes (For example 03301). When I use Word to merge labels, the zip codes that are supposed to have leading zeroes only show the next 4 digits with the leading zero missing. (For example 03301 shows as 3301.) I've noticed that when I edit the individual cells even though the zeroes are fine in the cell itself, the leading zero doesn't show in the edit line above the spread sheet. How do I get those leading zeroes to work? Thanks! J

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    You might want to explicitly make them text instead of formatted numbers. Add an apostrophe (') before adding the digits (and add the zero).
    OR
    Add a separte column for the output with the formula:
    =text(E1, "00000")
    where E1 is the cell with the zip "number"
    Or
    Another way to ensure excel or word does not "change it" is a "trick" used by text export programs and add a "sticky-space" before or after the number. Excel will sometimes remove regular spaces but not "sticky-spaces". A sticky space is ascii 160 you can add it by holding <alt> and entering 0160 from the num keypad.

    With a formula you can do:
    =text(E1, "00000")&char(160)
    or
    =char(160)&text(E1, "00000")

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    I'm afraid I'm not proficient enough to apply your suggestion without some help. When I create the label I just choose fields from the Excel spread sheet and space them in a format for an address label. How do I do that "picture switch" thing?
    Thanks! J

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    Hi Jaylin,

    In your mailmerge document, you'll have various fields that pull in the external data. If you go to Tools|Options|View in Word and select the 'Always' option for Field shading, the fields will appear with a grey background. If you select the one used for your zip code and press Shift-F9, the field will expand to look something like what I described in my last post, but without the picture switch. You may see a * MERGEFORMAT switch, though. Simply add the numeric picture switch (ie # 00000) - before the * MERGEFORMAT switch, if there is one.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    Hi jaylin,

    In Word, add a picture switch to your mergefield, thus:
    {MERGEFIELD ZipCode # 00000}

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    Thanks so much mate!
    You saved me hours of data editing frustration
    J

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Codes drop starting zeroes when merged (2003)

    Thanks for your help. I used the merge format approach rather than adding zeroes to the zips in all the records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •