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

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_lab...th_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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




kojaks43 wrote:
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

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Graham Mayor wrote:
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_lab...th_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.


kojaks43 wrote:
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

Thanks Graham. You are my hero. Originally I had the Excel column formatted
as SpecialZip+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
"Graham Mayor" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Graham Mayor wrote:
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_lab...th_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.


kojaks43 wrote:
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

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

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


kojaks43 wrote:
Thanks Graham. You are my hero. Originally I had the Excel column
formatted as SpecialZip+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
"Graham Mayor" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Graham Mayor wrote:
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_lab...th_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.


kojaks43 wrote:
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

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



"kojaks43" wrote:

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

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

"Annie Lord" wrote in message
...
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



"kojaks43" wrote:

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