Solved: Problem with retaining leading zero in mail merge.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Have you tried forcing an Excel Number format of "X" number of characters to the left of the decimal point?
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 

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

OBP

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

Attachments

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top