A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 9th, 2006, 02:41 AM posted to microsoft.public.excel.setup
J Slocum
external usenet poster
 
Posts: 1
Default 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  
Old August 9th, 2006, 12:42 PM posted to microsoft.public.excel.setup
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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  
Old August 9th, 2006, 12:44 PM posted to microsoft.public.excel.setup
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.