Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip code formatting
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Word to retain Excel formatting, eg zip code, ssn? | Mailmerge | |||
Why is there not a formatting code for a font change? | Microsoft Word Help | |||
zip code formatting merge | Mailmerge | |||
Formatting zip code in mail merge from Excel to Word | Microsoft Word Help | |||
Apply Style but keep direct formatting? | New Users |