Office & Productivity Software forum

General discussion

Excel-to-Word merge dropping zipcode digit

by val_nucci / October 24, 2003 11:13 PM PDT

Using Office XP, I have a mailing list in Excel. I have formatted one column of cells so that it is recognized as a zip code.

When I go to Word mail merge and print labels, the zip codes are missing the first

Post a reply
Discussion is locked
You are posting a reply to: Excel-to-Word merge dropping zipcode digit
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel-to-Word merge dropping zipcode digit
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Try again. Don't use the QUOTE mark.
by R. Proffitt Forum moderator / October 25, 2003 5:41 AM PDT

Your post is missing something.

Bob

Collapse -
Re:Try again. Don't use the QUOTE mark.
by val_nucci / October 26, 2003 8:52 AM PST

Thanks, Bob! Here goes:
I have a mailing list in Excel. I have formatted one column of cells so that it is recognized as a zip code.
On the Excel sheet all the zeros are there. When I print out the mailing list directly from Excel the zeros are all there.
Something happens between Excel and mail merge in Word. When I go through the process of creating labels, I open the Excel document, click OK and it is displayed in another format without the zero in the zip code.
So then when I go to print labels, the zip codes are missing the first zero (as in 06457) throughout.

Any ideas, anyone? Never experienced this with Office 2000 BTW.

Collapse -
Re:Re:Try again. Don't use the QUOTE mark.
by JethroUK / October 26, 2003 5:22 PM PST

i think it's because you're using an input mask to format as zip code - you're prolly wiser inputting phone numbers as 'text'

to convert your existing numbers

assuming your numbers are in col A, insert col B
B1 =A1 & ''
copy down as ness
to make these perminent
select column B
Edit/Copy
Edit/PasteSpecial Values
If this imports properly you can now delete the original column A

Collapse -
Re:Re:Re:Try again. Don't use the QUOTE mark.
by val_nucci / October 28, 2003 4:06 AM PST

Thank you, Jethro. I'll see how text format works and let you know.

Collapse -
Re:Re:Re:Try again. Don't use the QUOTE mark.
by val_nucci / October 29, 2003 11:48 AM PST

Tried putting in the column as text as suggested and then it dropped the first 0 of the zips right away on the spreadsheet before getting into the mail merge stuff. Perhaps I did not understand all the intructions.

I can put the floppy disk with the data into the 2000 version software, and labels come out all right.

And it is not just that my Excel data dates back to the older software, because I also tried starting a new address file on Excel and had the same problem.

Any suggestions?

Collapse -
Re:Re:Re:Re:Try again. Don't use the QUOTE mark.
by JethroUK / October 30, 2003 2:27 AM PST

it sounds likely that the preceding '0' is not actually there - it's displayed by the input mask - you could put it there:

assuming col A contains the original zips and Col B is empty

B1 = '0' & A1 & ''

Collapse -
Re:Re:Re:Re:Re:Try again. Don't use the QUOTE mark.
by val_nucci / October 30, 2003 8:27 PM PST

Thanks, Jethro. Good idea. Puzzling though as to why this behavior occurs at all in Excel XP, when Excel 2000 worked without needing these tweaks.

Collapse -
Leading zero issue
by N.T.Gray / October 6, 2007 1:06 AM PDT

I'm not familiar with the specific function for formatting ZIP codes. I do recall, though, that spreadsheets will drop a leading zero if they think the entry is a numerical value. To display the leading zero you have to identify it as text, which is most easily done by first typing an apostophe and then the numerals. Try that and see if it works.

Collapse -
You might want to do a length test...
by Keith Marcotte / October 31, 2003 11:08 AM PST

before adding the leading zero unless all zip codes have a leading zero (I'm Canadian, we use letters in our postal code in format x#x #x# so no leading zero problem).

Collapse -
!!!!!!!!!!!!HOW TO ACTUALLY FIX THIS ISSUE!!!!!!!!!!!
by juclark50 / July 30, 2007 3:38 AM PDT

To keep the formatting and all your preceding 0's follow these simple instructions.

In Word go to:
Tools--> Options --> under the General tab check "Confirm conversion at open" click ok

when you select a mail merge that has formatting, you will get a conversion option, select "MS Excel worsheets". should be the middle one. WALLA!!!! now wasn't that easier than adding quotes or changing to text.

Shame on those who give you incorrect answers.

Collapse -
And shame it only took 4 years for the answer.
by R. Proffitt Forum moderator / July 30, 2007 7:36 AM PDT

Do you work for some company in Redmond?

-> Disclaimer. Just kidding. I'm really happy you added a final cure to this but try to watch those posting dates.

Back then the forum had bugs with quote marks which derailed many discussions.

Bob

Collapse -
Thank you
by chongo86 / October 5, 2007 11:36 AM PDT

I disagree; posting dates are highly overrated.

Do you know how many google searches just lead you to an outdated forum? Thank you to the kind saint who answered this four year old question. (You'll also notice this is now #1 for Google under "dropping leading 0 in mail merge")

Collapse -
Many thanks...
by juclark50 / November 13, 2007 1:37 AM PST
In reply to: Thank you

Thanks for the kind words, this was my first post in this forum.

I do not work in Redmond, I live in Spokane, WA. I have been building gaming computers, fixing, modding, etc. for about 13 years.

Collapse -
Word 2007 Dropping 0
by xnatex21 / November 20, 2007 4:41 AM PST

Just to direct everyone who has unfortunately Upgraded to 07...Start at the Office Emblem in the upper left hand corner of a Word 07 document.
Once you click on it, at the bottom right of the drop down there is a button called 'Word Options" click it.
Then click the 'Advanced' button on the left hand side
Scroll all the way down and check the check box for "Confirm File Format Conversion on Open"

Wallla!
It works. I tested it.

Collapse -
I don't care how old the post is ...
by Fox35401 / June 20, 2008 8:49 AM PDT
In reply to: Word 2007 Dropping 0

It still works. Thanks for the help.

Collapse -
Word 2007 dropping leading zero
by davebopp1 / February 17, 2010 9:27 AM PST
In reply to: Word 2007 Dropping 0

Under advanced options I don't see the option to "Confirm File Format Conversion on Open"

Collapse -
but what if I am merging to MS Publisher?!
by btrandolph / December 4, 2007 2:39 AM PST

I am happy to hear about the Word fix; that will come in handy. However, I often merge address data onto postcards, mailers, etc created in MS Publisher (all 2003). I checked the location you specified and there is no option there to check the conversion.

Usually I follow the suggestion above and add a "0" before the zip field - we're in New England so most of the zips start with the zero. This time however, it's going all over so that trick won't do it. Also tried to merge in csv format.

Any Publisher users out there who can steer me to the setting adjustment or other hack?

thanks
Todd

Collapse -
THANK YOU THANK YOU
by mhammant / February 29, 2008 6:16 AM PST

I would just like to say Thank you. This has been an ongoing problem with our office and mail merges. After follwing your instructions, IT WORKED. Thanks

Collapse -
Dittos on the Thank Yous
by Jazzed99 / April 8, 2008 1:44 AM PDT

Imagine finding THE solution as an answer to a 4 year old question... Very slick...
Thanks again - this is saving our church tons of time since literally all of our mailings are sourced from SQL dumps to Excel. We'd been doing this as endless repetitions of "find/replace all" in Word.
A hint for future users: Existing Word mail merge docs need to be recreated from scratch. If you make the options change and use an existing doc, you don?t see the conversion option.
Thanks Again!

Collapse -
Thanks!!!
by stacmartin / January 13, 2009 10:51 PM PST

I've been manually typing in zeros for those silly zip codes, for an association mailing of about 300 labels.

I'm SO happy to have come upon your true "actual" fix for this problem. And it was so easy.

Thanks!

Collapse -
Thank You!!
by druidsmom / June 8, 2009 4:27 AM PDT

I know this post was about two years old, but I have been trying to find a fix for this problem for YEARS!!!! I do tons of mail merges and live in the northeast (where ALL the postal codes begin with zero) and this has been driving me nuts. Thank you so much for posting the solution! Many, many thanks.

Collapse -
6 years old, but who's counting?
by MarkFlax Forum moderator / June 8, 2009 6:37 AM PDT
In reply to: Thank You!!
Happy

Glad you found a solution here.

Mark
Collapse -
Separate worksheets within a workbook ?
by Pellis123 / September 27, 2009 11:57 PM PDT

Thank you for these directions-I'm just confused on one issue now..I have multiple worksheets within my file,and want to select different ones as my source for different mail merges. It does ask me whether I want to use the whole spreadsheet, but it doesn't give me any other choice--to move to individual worksheets.

Any suggestions?

Collapse -
THANK YOU!!!
by jjay422 / January 5, 2010 7:10 AM PST

I created an account on this site just to say THANK YOU juclark50! Your solution solved multiple masking issues when merging from Excel to Word in XP. You have saved me hours of work! THANK YOU SO VERY MUCH!! :-D

I would definitely advising against any ideas of removing this post thread no matter how old it is. Trust me when I say it is an issue that will be encountered again and again by new users.

Collapse -
I think the best way
by carolina1 / October 8, 2007 12:31 AM PDT

Just highlight the column that you intead to input the zip codes and then format the cells as text.
Then all your entries will be text with the zero(s) and will not be lost on the transfer.

It beats going back to repair each one.

Popular Forums
icon
Computer Help 49,613 discussions
icon
Computer Newbies 10,349 discussions
icon
Laptops 19,436 discussions
icon
Security 30,426 discussions
icon
TVs & Home Theaters 20,308 discussions
icon
Windows 10 360 discussions
icon
Phones 15,802 discussions
icon
Windows 7 7,351 discussions
icon
Networking & Wireless 14,641 discussions

CNET's Tech Minute

Top 3 news reading apps

With the latest tech, getting news delivered to your phone is easier than ever. Here's a roundup of apps that are customizable and useful for getting the news.