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?

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?

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?