| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I format Zip codes to print properly in a Mail merge?
I have created an Excel database containing addresses (more specifically zip
codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
Formatting a numeric value only changes the appearance of the number, but not
its value. So, even though you can see leading zeroes in you spreadsheet they are not part of the data. You can insert a helper column to the right of the zip code column and then use this formula to convert you zips to text and pad with a leading zero if necessary: =IF(LEN(A1)=4,"0"&TEXT(A1,"#"),TEXT(A1,"#")) Substituting the first zip code cell for A1 in the formula above and copy down the helper column to the last row having zip codes. Then copy the entire column containing your coversion formula and then move to the first cell containing you original zip code values. Click EDIT in the menu, select PASTE SPECIAL, click the VALUES option button and click OK. Delete the helper column and you're back in business. -- Kevin Backmann "Omega" wrote: I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
Kevin,
I will try this and let you know how it works. Thanks alot!! Omega "Kevin B" wrote: Formatting a numeric value only changes the appearance of the number, but not its value. So, even though you can see leading zeroes in you spreadsheet they are not part of the data. You can insert a helper column to the right of the zip code column and then use this formula to convert you zips to text and pad with a leading zero if necessary: =IF(LEN(A1)=4,"0"&TEXT(A1,"#"),TEXT(A1,"#")) Substituting the first zip code cell for A1 in the formula above and copy down the helper column to the last row having zip codes. Then copy the entire column containing your coversion formula and then move to the first cell containing you original zip code values. Click EDIT in the menu, select PASTE SPECIAL, click the VALUES option button and click OK. Delete the helper column and you're back in business. -- Kevin Backmann "Omega" wrote: I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
"Omega" wrote in message
... I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. Apparently the content of your cell is the number 8080, not the text string 08080. Changing the format as you've done doesn't alter the content of the cell, but if the cell is treated as a number it won't store the leading zeroes. You need to turn it into Text (not a number) & concatenate the leading zeroes. Try =REPT("0",5-LEN(A1))&A1 If you were starting from square one you could just format the cells as text *before* you entered the data. -- David Biddulph |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
David,
I am not starting from Zero. I have the zips already in the worksheet, but can not print them properly. I'm sorry to be such a novice at this, but I'm not sure how to formulate the entire column to accept the formulas that you have provided. Is there a way to insert the formalu that you have provided so that it will simply adjust the entries that I have made without going down the entire column with the new formula? Trying to get the hang of this so that I don't have to start over from the beginning. Your help is greatly appreciated. "David Biddulph" wrote: "Omega" wrote in message ... I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. Apparently the content of your cell is the number 8080, not the text string 08080. Changing the format as you've done doesn't alter the content of the cell, but if the cell is treated as a number it won't store the leading zeroes. You need to turn it into Text (not a number) & concatenate the leading zeroes. Try =REPT("0",5-LEN(A1))&A1 If you were starting from square one you could just format the cells as text *before* you entered the data. -- David Biddulph |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
Debra Dalgleish posted this:
There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/as...164951033.aspx And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill has instructions he http://sbarnhill.mvps.org/WordFAQs/C...ngWord2002.htm about half way down the page. Omega wrote: I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
"Omega" wrote in message
... "David Biddulph" wrote: "Omega" wrote in message ... I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. Apparently the content of your cell is the number 8080, not the text string 08080. Changing the format as you've done doesn't alter the content of the cell, but if the cell is treated as a number it won't store the leading zeroes. You need to turn it into Text (not a number) & concatenate the leading zeroes. Try =REPT("0",5-LEN(A1))&A1 If you were starting from square one you could just format the cells as text *before* you entered the data. I am not starting from Zero. I have the zips already in the worksheet, but can not print them properly. I'm sorry to be such a novice at this, but I'm not sure how to formulate the entire column to accept the formulas that you have provided. Is there a way to insert the formalu that you have provided so that it will simply adjust the entries that I have made without going down the entire column with the new formula? Trying to get the hang of this so that I don't have to start over from the beginning. Your help is greatly appreciated. Just copy the formula & paste it down the column, or drag the fill handle [where the cursor turns to a + over the square in the bottom right-hand corner of the cell] down the column (or just double-click on the fill handle). -- David Biddulph |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I format Zip codes to print properly in a Mail merge?
Dave,
That article did the trick. Thanks alot for your help and direction. Omega "Dave Peterson" wrote: Debra Dalgleish posted this: There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/as...164951033.aspx And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill has instructions he http://sbarnhill.mvps.org/WordFAQs/C...ngWord2002.htm about half way down the page. Omega wrote: I have created an Excel database containing addresses (more specifically zip codes). I then have attempted to mail merge them into Word and have been having a heck of a time getting the zip codes to print properly. Each time I attempt to print the labels (ie., zip code 08080) the first zero of the zip code will not not print for some reason. I have gone back and tried to change the format of the cell that holds the zip code and still it does not work. I have changed the format from Zip to custom (00000) and still no success. Any suggestions from others who have had the same problem and found a remedy would be greatly appreciated. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What format do I use to mail merge percentages? | Excel Discussion (Misc queries) | |||
| Envelopes will not print using mail merge. | Excel Discussion (Misc queries) | |||
| mail merge problems | Setting up and Configuration of Excel | |||
| How to filter on a date field during mail merge | Excel Worksheet Functions | |||
| Mail Merge - format of numbers | Excel Discussion (Misc queries) | |||
Linear Mode
