Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
zip code with mail merge
the 1st zero gets dropped from zip codes when address spreadsheet is used for
mail merge. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fix loosing 1st digit in zip code in mail merge? | Excel Worksheet Functions | |||
nine digit zip code won't print in mail merge | Excel Discussion (Misc queries) | |||
Code launches Mail Merge but disables the Mail Merge | Excel Discussion (Misc queries) | |||
doing mail merge with excel the first letter in zip code is missi | Excel Discussion (Misc queries) | |||
How do I input zeroes in a zip code for a mail merge? | Excel Worksheet Functions |