Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
I have Excel spreadsheet with cells/column that contain either 5 or 9 zip
code numbers. I want the zip code numbers to print in my merged Word document as what they are, either 5 or 9 digits and blank if nothing. But I keep getting error code as noted below when inputting the correct merge code in the Word document. I am using XP Pro, Microsoft Small Business Exchange Server with Office 2007 Ultimate version. Excel 2007 xls spreadsheet as DB. Column formatted as numbers with both 5 and 9 digits in cells. Numbers are entered as continuous number of 5 or 9 digits. Word merge is formatted as per Graham Mayor article "Formatting Word Fields." I have checked and re-checked my typing and it is exactly as Mr. Mayor indicates in his article. I have read every other post on this discussion group regarding zip code formatting and tried all options offered. None work for me. I am using the CTRL+F9 for the IF statement and using the insert merge field choice on the Word ribbon. My problem is that when I set up my mailmerge formatting in Word document for the zip code field I get the following error message. Error! Unknown op code for conditional. This is the code that I am typing: {IF{MERGEFIELD Employer Zip} 99999"{MERGEFIELD Employer Zip\#"00000'-'0000"}""{MERGEFIELD Employer Zip\#"00000"}"} as noted in Mr. Mayor's examples I have tried: removing the '-'. adding the ;; different spacing around the merge code and \ replacing the 99999 with 00000 Nothing is working. What am I doing wrong? Any help would be greatly appreciated. -- John R. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
1. As posted, the code you are using would not work anyway, but the
chances are that it isn't quite what you are actually using in your Word document... 1. What is the name of the Excel field containing your Zip code? If Word thinks the name has a space in it, you will need to surround the name by double quotes, e.g. { MERGEFIELD "Employer Zip" \#00000 } or maybe the name has an underscore in it, in which case { MERGEFIELD Employer_Zip \#00000 } should be OK. 2. If you have amerge field that just has { MERGEFIELD Employer_Zip } (or whatever is the correct field for the name you actually have) what does it actually display in Word when you preview the data for a. a 5-digit Zip b. a 9-digit Zip ? NB, Excel can store Zip codes in a number of different ways - e.g. a 9-digit Zip might be stored as a nine-digit number with cell formatting that causes Excel to display it as e.g. 12345-6789, or it might be stored as a 10-digit string that contains the "-". As long as all the ZIP codes in the column are stored as numbers (and possibly in addition, there are no blanks), the approach on Graham Mayor's page should work. If some are stored as strings with "-", it may not do so. Peter Jamieson http://tips.pjmsn.me.uk John wrote: I have Excel spreadsheet with cells/column that contain either 5 or 9 zip code numbers. I want the zip code numbers to print in my merged Word document as what they are, either 5 or 9 digits and blank if nothing. But I keep getting error code as noted below when inputting the correct merge code in the Word document. I am using XP Pro, Microsoft Small Business Exchange Server with Office 2007 Ultimate version. Excel 2007 xls spreadsheet as DB. Column formatted as numbers with both 5 and 9 digits in cells. Numbers are entered as continuous number of 5 or 9 digits. Word merge is formatted as per Graham Mayor article "Formatting Word Fields." I have checked and re-checked my typing and it is exactly as Mr. Mayor indicates in his article. I have read every other post on this discussion group regarding zip code formatting and tried all options offered. None work for me. I am using the CTRL+F9 for the IF statement and using the insert merge field choice on the Word ribbon. My problem is that when I set up my mailmerge formatting in Word document for the zip code field I get the following error message. Error! Unknown op code for conditional. This is the code that I am typing: {IF{MERGEFIELD Employer Zip} 99999"{MERGEFIELD Employer Zip\#"00000'-'0000"}""{MERGEFIELD Employer Zip\#"00000"}"} as noted in Mr. Mayor's examples I have tried: removing the '-'. adding the ;; different spacing around the merge code and \ replacing the 99999 with 00000 Nothing is working. What am I doing wrong? Any help would be greatly appreciated. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
Below is exactly how I have the Word 2007 doc coded:
{IF{MERGEFIELD EmployerZip} 99999"{MERGEGIELD EmployerZip\#"00000'-'0000"}" "{MERGEFIELD EmployerZip\#"00000"}"} When I ALT+F9 I get an error code of Error! Unknown op code for conditional for the above. My Excel columns are stored as numbers (no special zip or special zip+4) via the column and each cell. I have 9 dummy rows at the top of my spreadsheet before the actual data is recorded. I have 5 and 9 digit zip codes. The 9 digit codes are continuous in Excel without any "-". I tried that also and it did not work. In the Word merge code I have copies exactly as per Mr. Mayor's Formatting Word Fields article has suggested. I also inserted the ;; to see if that would make any difference and it does not. As to your questions in your post: 1 the Excel Field name is EmployerZip with no spaces. In my Word Doc I used the Insert Merge Field under the Mailings Tab to insert the correct mergefield code. I tried your suggestion anyway as to adding the quotation marks around the EmployerZip portion and that did not help. 2. What displays for a 5 digit zip is 00006-7951 What displays for a 9 digit zip is 67951-4327 That is if I have the colum coded in Excel as special zip code and not special zip+4. I tried zip+4 on the 9 digit and that did not put the "-" in the zip code in Word. I went back to formatting Excel column and individual cells as numbers and each zip code is either 5 or 9 digits in each cell. If 9 digit then as one continuous number with no spaces or "-". I should also tell you that none of the zip codes in my Excel spreadsheet contain a leading zero. -- John R. "Peter Jamieson" wrote: 1. As posted, the code you are using would not work anyway, but the chances are that it isn't quite what you are actually using in your Word document... 1. What is the name of the Excel field containing your Zip code? If Word thinks the name has a space in it, you will need to surround the name by double quotes, e.g. { MERGEFIELD "Employer Zip" \#00000 } or maybe the name has an underscore in it, in which case { MERGEFIELD Employer_Zip \#00000 } should be OK. 2. If you have amerge field that just has { MERGEFIELD Employer_Zip } (or whatever is the correct field for the name you actually have) what does it actually display in Word when you preview the data for a. a 5-digit Zip b. a 9-digit Zip ? NB, Excel can store Zip codes in a number of different ways - e.g. a 9-digit Zip might be stored as a nine-digit number with cell formatting that causes Excel to display it as e.g. 12345-6789, or it might be stored as a 10-digit string that contains the "-". As long as all the ZIP codes in the column are stored as numbers (and possibly in addition, there are no blanks), the approach on Graham Mayor's page should work. If some are stored as strings with "-", it may not do so. Peter Jamieson http://tips.pjmsn.me.uk John wrote: I have Excel spreadsheet with cells/column that contain either 5 or 9 zip code numbers. I want the zip code numbers to print in my merged Word document as what they are, either 5 or 9 digits and blank if nothing. But I keep getting error code as noted below when inputting the correct merge code in the Word document. I am using XP Pro, Microsoft Small Business Exchange Server with Office 2007 Ultimate version. Excel 2007 xls spreadsheet as DB. Column formatted as numbers with both 5 and 9 digits in cells. Numbers are entered as continuous number of 5 or 9 digits. Word merge is formatted as per Graham Mayor article "Formatting Word Fields." I have checked and re-checked my typing and it is exactly as Mr. Mayor indicates in his article. I have read every other post on this discussion group regarding zip code formatting and tried all options offered. None work for me. I am using the CTRL+F9 for the IF statement and using the insert merge field choice on the Word ribbon. My problem is that when I set up my mailmerge formatting in Word document for the zip code field I get the following error message. Error! Unknown op code for conditional. This is the code that I am typing: {IF{MERGEFIELD Employer Zip} 99999"{MERGEFIELD Employer Zip\#"00000'-'0000"}""{MERGEFIELD Employer Zip\#"00000"}"} as noted in Mr. Mayor's examples I have tried: removing the '-'. adding the ;; different spacing around the merge code and \ replacing the 99999 with 00000 Nothing is working. What am I doing wrong? Any help would be greatly appreciated. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
1. Put a space before 99999
2. (I assume "MERGEGIELD" is a typo :-) ) Peter Jamieson http://tips.pjmsn.me.uk John wrote: Below is exactly how I have the Word 2007 doc coded: {IF{MERGEFIELD EmployerZip} 99999"{MERGEGIELD EmployerZip\#"00000'-'0000"}" "{MERGEFIELD EmployerZip\#"00000"}"} When I ALT+F9 I get an error code of Error! Unknown op code for conditional for the above. My Excel columns are stored as numbers (no special zip or special zip+4) via the column and each cell. I have 9 dummy rows at the top of my spreadsheet before the actual data is recorded. I have 5 and 9 digit zip codes. The 9 digit codes are continuous in Excel without any "-". I tried that also and it did not work. In the Word merge code I have copies exactly as per Mr. Mayor's Formatting Word Fields article has suggested. I also inserted the ;; to see if that would make any difference and it does not. As to your questions in your post: 1 the Excel Field name is EmployerZip with no spaces. In my Word Doc I used the Insert Merge Field under the Mailings Tab to insert the correct mergefield code. I tried your suggestion anyway as to adding the quotation marks around the EmployerZip portion and that did not help. 2. What displays for a 5 digit zip is 00006-7951 What displays for a 9 digit zip is 67951-4327 That is if I have the colum coded in Excel as special zip code and not special zip+4. I tried zip+4 on the 9 digit and that did not put the "-" in the zip code in Word. I went back to formatting Excel column and individual cells as numbers and each zip code is either 5 or 9 digits in each cell. If 9 digit then as one continuous number with no spaces or "-". I should also tell you that none of the zip codes in my Excel spreadsheet contain a leading zero. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
Thank you Peter!
Yes, it was a typo. I know I tried putting a space in the same place you suggested here today. But, apparently tried it in conjuction with other choices and it did not work then so moved on to something else. I have looked and checked my typing against Mr. Mayor's suggested if statements a hundred times. I know it alway comes down to some minor and overlooked error. You have saved my day. Thank you. -- John R. "Peter Jamieson" wrote: 1. Put a space before 99999 2. (I assume "MERGEGIELD" is a typo :-) ) Peter Jamieson http://tips.pjmsn.me.uk John wrote: Below is exactly how I have the Word 2007 doc coded: {IF{MERGEFIELD EmployerZip} 99999"{MERGEGIELD EmployerZip\#"00000'-'0000"}" "{MERGEFIELD EmployerZip\#"00000"}"} When I ALT+F9 I get an error code of Error! Unknown op code for conditional for the above. My Excel columns are stored as numbers (no special zip or special zip+4) via the column and each cell. I have 9 dummy rows at the top of my spreadsheet before the actual data is recorded. I have 5 and 9 digit zip codes. The 9 digit codes are continuous in Excel without any "-". I tried that also and it did not work. In the Word merge code I have copies exactly as per Mr. Mayor's Formatting Word Fields article has suggested. I also inserted the ;; to see if that would make any difference and it does not. As to your questions in your post: 1 the Excel Field name is EmployerZip with no spaces. In my Word Doc I used the Insert Merge Field under the Mailings Tab to insert the correct mergefield code. I tried your suggestion anyway as to adding the quotation marks around the EmployerZip portion and that did not help. 2. What displays for a 5 digit zip is 00006-7951 What displays for a 9 digit zip is 67951-4327 That is if I have the colum coded in Excel as special zip code and not special zip+4. I tried zip+4 on the 9 digit and that did not put the "-" in the zip code in Word. I went back to formatting Excel column and individual cells as numbers and each zip code is either 5 or 9 digits in each cell. If 9 digit then as one continuous number with no spaces or "-". I should also tell you that none of the zip codes in my Excel spreadsheet contain a leading zero. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Zip Code formatting Problem Word 2007
Glad it's sorted. These "little" syntax issues can indeed be a pain.
Peter Jamieson http://tips.pjmsn.me.uk John wrote: Thank you Peter! Yes, it was a typo. I know I tried putting a space in the same place you suggested here today. But, apparently tried it in conjuction with other choices and it did not work then so moved on to something else. I have looked and checked my typing against Mr. Mayor's suggested if statements a hundred times. I know it alway comes down to some minor and overlooked error. You have saved my day. Thank you. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting problem in WORD 2007 | New Users | |||
Word 2007 formatting problem | Microsoft Word Help | |||
Can Word 2007 apply a zip code bar code to envelopes? | Microsoft Word Help | |||
How do I get Word to retain Excel formatting, eg zip code, ssn? | Mailmerge | |||
Formatting zip code in mail merge from Excel to Word | Microsoft Word Help |