If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do you enter zip codes starting with 0 for a mail merge
I have an address list created in Excel which I'm trying to export into a
mail merge in Word. Most of my zip codes begin with a "0" and although I'm putting an apostrophe before the zero it doesn't carry over into the mail merge so I'm coming up with a 4 number zip code instead of a full zip code. I've tried setting the format as text and as special/zip code. I was been able to make it work in one worksheet a long time ago but now it doesn't work in the others - what am I missing??? Anyone willing to help? |
#2
|
|||
|
|||
How do you enter zip codes starting with 0 for a mail merge
You could try this formula in a helper column:
=IF(LEN(A1)5,REPT("0"," - "&5-LEN(A1))&A1," - "&A1) This will add leading zeroes to the code (assumed to be in column A - adjust as necessary) to make it 5 characters, and will convert the code to text with a dash in front of it. Copy the formula down the column and fix the values using copy Edit | Paste Special | Values (check) | OK and Esc. You can then replace your original codes with these modified ones. Hope this helps. Pete J Slocum wrote: I have an address list created in Excel which I'm trying to export into a mail merge in Word. Most of my zip codes begin with a "0" and although I'm putting an apostrophe before the zero it doesn't carry over into the mail merge so I'm coming up with a 4 number zip code instead of a full zip code. I've tried setting the format as text and as special/zip code. I was been able to make it work in one worksheet a long time ago but now it doesn't work in the others - what am I missing??? Anyone willing to help? |
#3
|
|||
|
|||
How do you enter zip codes starting with 0 for a mail merge
Sorry, the formula should be:
=IF(LEN(A1)5," - "&REPT("0",5-LEN(A1))&A1," - "&A1) Hope this helps. Pete Pete_UK wrote: You could try this formula in a helper column: =IF(LEN(A1)5,REPT("0"," - "&5-LEN(A1))&A1," - "&A1) This will add leading zeroes to the code (assumed to be in column A - adjust as necessary) to make it 5 characters, and will convert the code to text with a dash in front of it. Copy the formula down the column and fix the values using copy Edit | Paste Special | Values (check) | OK and Esc. You can then replace your original codes with these modified ones. Hope this helps. Pete J Slocum wrote: I have an address list created in Excel which I'm trying to export into a mail merge in Word. Most of my zip codes begin with a "0" and although I'm putting an apostrophe before the zero it doesn't carry over into the mail merge so I'm coming up with a 4 number zip code instead of a full zip code. I've tried setting the format as text and as special/zip code. I was been able to make it work in one worksheet a long time ago but now it doesn't work in the others - what am I missing??? Anyone willing to help? |
Thread Tools | |
Display Modes | |
|
|