Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging with Excel data source (Word & Excel 2003 SP1)

    I am trying to merge using data in an Excel Spreadsheet. All works well except for the zipcodes. Whether the Excel column is defined as special, zipcode or text, the leading zeros drop when the merge is executed. Help!!!

    TIA,
    gg

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Have you tried forcing the strings with leading zeroes to keep 'em by entering them as, eg, '00045678 (note the ' in front of the string)?

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Thanks for your reply, but I don't understand how to do this. Can you please be more specific?

    My tentative solution was to make the cells "text" in Excel, copy and paste the data into a Word table, and merge from there.

    However, I would like to merge the data directly from Excel, if I can get it to work.

    gg

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Re-type the zipcodes in, if necessary.
    So, if the code is
    000234567 [I don't know what a zip code looks like]
    re-type as follows, but omitting the spaces, which I've put in for clarity
    ' 0 0 0 2 3 4 5 6 7
    The significant character is the
    '
    (the single quote character, also used as an apostrophe)

  5. #5
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Thanks, I will try that. The single apostrophe denotes a "text" field to Excel, right? Yet when I highlighted the column and said format as a "text" field it did not seem to work.

    I will try your method.

    Thanks,
    gg

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

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    But did you format the column as text before or after entering the zip codes? It won't help if you format it after entering the data, but it should if you do if before.

  7. #7
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Got it! But I think that I formatted at text, added in the leading zeros, tried the merge, but it did not work.

    At this point I executed alternate plan B: exported the data from Excel to Word, put in leading zeros, and did the merge.

    I will try the experiment again in the morning... 11:20 PM here and I must turn in!

    Thanks, everyone for your help.
    gg

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

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Hi gg,

    The simple solution is to format your MERGEFIELD with a numeric picture switch. For example, adding:
    # "00000
    to the MERGEFIELD's code will force the merged value to be five digits long, with leading 0s for shorter numbers.

    If your postcodes are in the format "1234-5678", a bit more field coding applied to the MERGEFIELD can also be used to return a correctly formatted output.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Is this done at the Word end?
    gg

  10. #10
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    That is what I thought I did. See attached for the message.
    gg
    Attached Files Attached Files

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

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    Hi gg,

    Yes, in Word you'd select the MERGEFIELD and press Shift-F9 to reveal its code. It should look something like:
    {MERGEFIELD Postcode}

    You simply edit the field by adding the switch (# "00000), so that it looks like:
    {MERGEFIELD Postcode # "00000}
    then press F9 to update the field. Of course, use however many 0s in the switch that you need to maintain the required number of digits.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Merging with Excel data source (Word & Excel 2003 SP1)

    The error indicator you get is a feature introduced in Excel 2002 (I think). Excel flags all kinds of "unexpected" values as potential errors. In this case, it is what you intend.

    I don't have Office 2003. In Office XP, the values are merged into Word including leading zeros, whatever method I select to connect to the Excel data source.

Posting Permissions

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