Zip code formatting

K

kojaks43

I used this site and found some great information.
http://www.gmayor.com/formatting_word_fields.htm

My Excel file contains zip codes that are 00000 and 00000-0000. One of the
suggestions in the above site allows me to make all my 00000 zip codes add
actual 0000's to the code so it appears as 12345-0000. I can live with that,
assuming the post office can. I would prefer if Word could distinguish my 5
digit zips from my 9 digit zips and output just what was in Excel. However,
if it can not, I'll live.

However, when I use the IF statement as shown in the above site, and then
tell Word to Update all Labels in the merge, I get a "," and "-0000" (w/o the
quote marks) in every label on the sheet. i.e. if there are ten blank labels
on the sheet, and I am only merging four names and addresses, the "," and
"-0000" populate the remaining six labels. The only place that has a "," and
the "-0000" is the comma between city and state fields and the "-0000" comes
from the If statement that has no zip code.

I've checked the characters in the merge fields and all seems to be fine.

What have I done wrong??

Your guidance is appreciated.

Oh yeah, I am using Word 2003 and Windows 2000.

kojak
 
G

Graham Mayor

If you have some entries formatted as 4 digit zips and some as 9 digit zips
rather than all as numbers, then the simplest solution is to format the
column as text and use the alternative connection method described in the
Excel data section of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm . Then you won't
need the conditional statement. Simply insert the ZIp field without a
switch.
Currently the '-' in the field conspires against you. The conditional field
would work if you removed that.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
G

Graham Mayor

To clarify, if you want the method suggested in the original link to work,
the manually inserted hyphens in the zip column must be removed, the column
formatted as numbers then the two types of zip should be formatted as five
and nine digit zips respectively. You cannot perform calculations on text.

As for the calculation at http://www.gmayor.com/formatting_word_fields.htm,
to give you the version that you require, the switch should be modified as
follows :

{ IF{ Mergefield Zip } > 99999 "{ Mergefield Zip \# "00000'-'00000" }" "{
Mergefield Zip \# "00000" }" }

All field boundary pairs are entered using CTRL+F9.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
K

kojaks43

Thanks Graham. You are my hero. Originally I had the Excel column formatted
as Special>Zip+4. I changed it to just numbers as you instructed. I placed
in the new IF statement code and it worked fine. There seemed to be some
stumbling between Excel and Word (screen flutters and the like), but it all
worked.

I saved it as a template, and that seemed to work as well.

I'm a happy guy. Thanks
k
 
G

Graham Mayor

I have updated the original web page for the sake of clarification.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
A

Annie Lord

HELP!!! I've tried many times to type in the IF command about 9 digit zip
codes that is at gmayor.com/formatting_word_fields and nothing is working.
What's the trick?
Annie
 
D

Doug Robbins - Word MVP

Are you using Ctrl+F9 to insert the field delimiters { }?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top