Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge Leading 0's (Publisher 2003)

    I am trying to help someone mail merge from Excel to Publisher postcards and everything is working fine except the leading zero's are being truncated.
    I have the instructions and fixed this in Word by following these instructions (from someone here at the lounge) : In Word, select Tools | Options...Activate the General tab. Tick the box labeled "Confirm conversion at Open". Now proceed to step 3 of the Mail Merge Wizard again, and click "Browse..."(if you're starting anew) or "Select a different list..." (if you already created the merge document) After selecting the Excel workbook, you will be prompted to confirm the data source. The default choice is OLE DB. Select MS Excel worksheets via DDE instead, and click OK. Number formatting from Excel will now be preserved in the merged document.
    I cannot figure out how to help them in publisher?

    Thanks for any info
    Melanie

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge Leading 0's (Publisher 2003)

    > from someone here at the lounge

    That was me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    As you have found, Publisher doesn't offer as much control as Word does. Unless someone with more Publisher experience than I have has a better suggestion, you'll have to add a column in the data source containing the numbers as text in the desired format. For example, if the numbers are in column A, starting in A2, and if you want 4 digits with leading zeros if necessary, put the following formula in B2:

    =TEXT(A2,"0000")

    and fill down as far as needed. Enter an appropriate column heading in B1. Use this column (field) in the mail merge instead of the original one.

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

    Re: Mail Merge Leading 0's (Publisher 2003)

    Thank you for your response! (and thanks for helping the first time!!)

    We still cannot get the zip code to display in publisher - we do not need 4 leading zeros - just a 5 number zip code 03456 - The zipcode column in excel is formatted special with the type as zip code. We followed your instructions and formatted a new column as text but it only displays the zip as 3456.

    Must be doing something incorrect, not sure what it is though.

    Melanie

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge Leading 0's (Publisher 2003)

    If you want 5 digits, use a formula such as

    =TEXT(A2,"00000")

    The number of zeros in the second argument is the total number of digits you need (not just leading zeros).

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

    Re: Mail Merge Leading 0's (Publisher 2003)

    of couse......So simple.....wasn't thinking...

    thank you!

Posting Permissions

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