Home » Category » Microsoft Excel

Microsoft Excel: zip code with mail merge

205| Thu, 22 May 2008 07:02:00 GMT| anonymous| Comments (4)
the 1st zero gets dropped from zip codes when address spreadsheet is used for mail merge.

Keywords & Tags: zip, code, mail, merge, microsoft, excel

URL: http://developertags.com/microsoft-excel/495094/
 
«« Prev - Next »» 4 helpful answers below.
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.

jmbundy | Thu, 22 May 2008 07:03:00 GMT |

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.

bonim | Thu, 22 May 2008 07:04:00 GMT |

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.

bethatthechamber | Thu, 22 May 2008 07:05:00 GMT |

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" <BethattheChamber...discussions.microsoft.com> wrote in
message news:A095B619-46A4-4FF1-BE18-DEEE96339B34...microsoft.com...
>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.

david | Thu, 22 May 2008 07:06:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories