Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default zip code with mail merge

the 1st zero gets dropped from zip codes when address spreadsheet is used for
mail merge.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 772
Default zip code with mail merge

I hope this isn't the same person posting from yesterday!! if so send me your
stuff, if not try =text(A1,"00000") then you can copy and paste special
values over your old data.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Booklisa" wrote:

the 1st zero gets dropped from zip codes when address spreadsheet is used for
mail merge.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 353
Default zip code with mail merge

The first zero is getting dropped because it isn't really there - your cell
has just been formatted to show one. To format your merge field so it will
do the same thing - open your mail merge document, press Alt+F9 to show field
codes, for your zip code field, add the following switch after the field name
and before the bracket:
\# 00000
if your zip code field name is zip - it would look like this:
{ MERGEFIELD zip \# 00000}

Alt+F9 again to hide the field codes - when merge data is displayed, you
should have your leading zero's back.

"Booklisa" wrote:

the 1st zero gets dropped from zip codes when address spreadsheet is used for
mail merge.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default zip code with mail merge

I am wondering why Office 2007 made this so complicated and if there is a way
to save this as the default field name. My officemates and I do mail merges
very often and this is an extra step instead of an advancement of technology.
Thank you for solving this mystery.
Beth

"BoniM" wrote:

The first zero is getting dropped because it isn't really there - your cell
has just been formatted to show one. To format your merge field so it will
do the same thing - open your mail merge document, press Alt+F9 to show field
codes, for your zip code field, add the following switch after the field name
and before the bracket:
\# 00000
if your zip code field name is zip - it would look like this:
{ MERGEFIELD zip \# 00000}

Alt+F9 again to hide the field codes - when merge data is displayed, you
should have your leading zero's back.

"Booklisa" wrote:

the 1st zero gets dropped from zip codes when address spreadsheet is used for
mail merge.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 903
Default zip code with mail merge

Rather than using numbers of zipcodes, I would suggest
that you format the column as Text in Excel before entry,
that would mean that you can have both 5 digit and 9 digit
US zip codes, and you can use zip codes used in other
countries.

Another method would be to add another column in Excel
=TEXT(E5,"00000") or to use the macro FixUSzip5
suggested on my Mail Merge page.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

Haven't actually tried these on Excel 2007 yet, but they
worked fine on Excel 2000 and Excel 2002.
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Beth at the Chamber" wrote in
message ...
I am wondering why Office 2007 made this so complicated and if there is a
way
to save this as the default field name. My officemates and I do mail
merges
very often and this is an extra step instead of an advancement of
technology.
Thank you for solving this mystery.
Beth

"BoniM" wrote:

The first zero is getting dropped because it isn't really there - your
cell
has just been formatted to show one. To format your merge field so it
will
do the same thing - open your mail merge document, press Alt+F9 to show
field
codes, for your zip code field, add the following switch after the field
name
and before the bracket:
\# 00000
if your zip code field name is zip - it would look like this:
{ MERGEFIELD zip \# 00000}

Alt+F9 again to hide the field codes - when merge data is displayed, you
should have your leading zero's back.

"Booklisa" wrote:

the 1st zero gets dropped from zip codes when address spreadsheet is
used for
mail merge.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I fix loosing 1st digit in zip code in mail merge? Lori Excel Worksheet Functions 2 April 28th 10 03:06 AM
nine digit zip code won't print in mail merge Durham Excel Discussion (Misc queries) 11 July 1st 08 12:34 PM
Code launches Mail Merge but disables the Mail Merge austris Excel Discussion (Misc queries) 0 October 14th 06 01:11 AM
doing mail merge with excel the first letter in zip code is missi Andrew A. McBain DC Excel Discussion (Misc queries) 1 August 29th 06 01:26 PM
How do I input zeroes in a zip code for a mail merge? S_Suarez Excel Worksheet Functions 2 December 9th 05 04:58 PM


All times are GMT +1. The time now is 08:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"