Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Problem with retaining leading zero in mail merge.


(!)

Leslie Scooter's Avatar
Leslie Scooter Leslie Scooter is offline
Computer Specs
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Mar 2008
Experience: Intermediate
14-May-2008, 07:21 PM #1
Solved: Problem with retaining leading zero in mail merge.
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
Leslie Scooter's Avatar
Leslie Scooter Leslie Scooter is offline
Computer Specs
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Mar 2008
Experience: Intermediate
20-May-2008, 10:34 AM #2
bump
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,541 posts.
 
Join Date: Mar 2005
Location: UK
20-May-2008, 10:59 AM #3
Have you tried forcing an Excel Number format of "X" number of characters to the left of the decimal point?
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
20-May-2008, 11:15 AM #4
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's Avatar
Leslie Scooter Leslie Scooter is offline
Computer Specs
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Mar 2008
Experience: Intermediate
28-May-2008, 11:43 AM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,541 posts.
 
Join Date: Mar 2005
Location: UK
28-May-2008, 12:15 PM #6
Leslie, select "Custom" instead of Number and enter 000000
ie as many zeroes as you need.
See the attached sheet
Attached Files
File Type: xls Forcedzeroes.xls (13.5 KB, 843 views)
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-May-2008, 12:21 PM #7
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,541 posts.
 
Join Date: Mar 2005
Location: UK
28-May-2008, 12:26 PM #8
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-May-2008, 02:23 PM #9
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's Avatar
Leslie Scooter Leslie Scooter is offline
Computer Specs
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Mar 2008
Experience: Intermediate
28-May-2008, 03:33 PM #10
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.
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-May-2008, 03:38 PM #11
Not a problem, if you need further assistance feel free to post again.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Mail Merge Problem CNHalsey Business Applications 2 21-Jun-2007 02:29 PM
Access and mail merge problem fcuenca Business Applications 10 07-Mar-2006 12:16 PM
mail merge problem steviep6a Business Applications 3 14-Feb-2006 03:34 PM
Excel merge drops leading zip code zeroes jaylin Business Applications 5 07-Dec-2003 07:44 PM
Mail merge - dropping zipcode leading zero boxergal Business Applications 3 21-Nov-2003 10:16 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑