How do you enter zip codes starting with 0 for a mail merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J Slocum - 09 Aug 2006 02:41 GMT

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?

Reply to this Message

Pete_UK - 09 Aug 2006 12:42 GMT

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

> 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
[quoted text clipped - 4 lines]
> in the others - what am I missing???
> Anyone willing to help?

Reply to this Message

Pete_UK - 09 Aug 2006 12:44 GMT

Sorry, the formula should be:

=IF(LEN(A1)<5," - "&REPT("0",5-LEN(A1))&A1," - "&A1)

Hope this helps.

Pete

> You could try this formula in a helper column:
>
[quoted text clipped - 19 lines]
> > in the others - what am I missing???
> > Anyone willing to help?

Reply to this Message