Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default How to enter zip starting with 0 (zero)

Hi,

I am entering addresses into an Excel worksheet the I need to use in a mail
merge. One of the addresses has a five digit postal zip code that begins
with a "0" (zero.) I typed it in like any other zip code entry, but Excel
dropped the zero as soon as I moved to the next cell. I tried converting the
entry into "text," but when I printed the record, only the zero printed.

Other zip codes that do not start with zero work fine in a mail merge and in
printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie



  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 964
Default How to enter zip starting with 0 (zero)

Format the cell as text before entering

--


Regards,


Peo Sjoblom

"Orrie" wrote in message
...
Hi,

I am entering addresses into an Excel worksheet the I need to use in a
mail merge. One of the addresses has a five digit postal zip code that
begins with a "0" (zero.) I typed it in like any other zip code entry, but
Excel dropped the zero as soon as I moved to the next cell. I tried
converting the entry into "text," but when I printed the record, only the
zero printed.

Other zip codes that do not start with zero work fine in a mail merge and
in printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie





  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default How to enter zip starting with 0 (zero)

Thanks Peo, it worked, although I had to try it twice until it did. The
first time, there was a little error icon next to the cell. The error was
supposed to be an apostrophe preceding the numbers. There was no apostrophe,
but I deleted the entry, retyped it and then selected the setting in Format
Cell to accept the entry with the "error," the entry worked.

Orrie

"Peo Sjoblom" wrote in message
...
Format the cell as text before entering

--


Regards,


Peo Sjoblom

"Orrie" wrote in message
...
Hi,

I am entering addresses into an Excel worksheet the I need to use in a
mail merge. One of the addresses has a five digit postal zip code that
begins with a "0" (zero.) I typed it in like any other zip code entry,
but Excel dropped the zero as soon as I moved to the next cell. I tried
converting the entry into "text," but when I printed the record, only the
zero printed.

Other zip codes that do not start with zero work fine in a mail merge and
in printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie








  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to enter zip starting with 0 (zero)

The reason is that you have error checking turned on and it warns against
text numbers because 9/10 you wouldn't want that but unless you want to add
some switches in Word that's the way to go


--


Regards,


Peo Sjoblom



"Orrie" wrote in message
...
Thanks Peo, it worked, although I had to try it twice until it did. The
first time, there was a little error icon next to the cell. The error was
supposed to be an apostrophe preceding the numbers. There was no
apostrophe, but I deleted the entry, retyped it and then selected the
setting in Format Cell to accept the entry with the "error," the entry
worked.

Orrie

"Peo Sjoblom" wrote in message
...
Format the cell as text before entering

--


Regards,


Peo Sjoblom

"Orrie" wrote in message
...
Hi,

I am entering addresses into an Excel worksheet the I need to use in a
mail merge. One of the addresses has a five digit postal zip code that
begins with a "0" (zero.) I typed it in like any other zip code entry,
but Excel dropped the zero as soon as I moved to the next cell. I tried
converting the entry into "text," but when I printed the record, only
the zero printed.

Other zip codes that do not start with zero work fine in a mail merge
and in printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie










  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 793
Default How to enter zip starting with 0 (zero)

The other option is to Format the cells as Custom-00000. This way nos will
be stored as numbers though I can't think of any use.

"Orrie" wrote:

Thanks Peo, it worked, although I had to try it twice until it did. The
first time, there was a little error icon next to the cell. The error was
supposed to be an apostrophe preceding the numbers. There was no apostrophe,
but I deleted the entry, retyped it and then selected the setting in Format
Cell to accept the entry with the "error," the entry worked.

Orrie

"Peo Sjoblom" wrote in message
...
Format the cell as text before entering

--


Regards,


Peo Sjoblom

"Orrie" wrote in message
...
Hi,

I am entering addresses into an Excel worksheet the I need to use in a
mail merge. One of the addresses has a five digit postal zip code that
begins with a "0" (zero.) I typed it in like any other zip code entry,
but Excel dropped the zero as soon as I moved to the next cell. I tried
converting the entry into "text," but when I printed the record, only the
zero printed.

Other zip codes that do not start with zero work fine in a mail merge and
in printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie











  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to enter zip starting with 0 (zero)

The OP wants to use it for mailmerge in Word then it doesn't matter how they
are numerically formatted


--


Regards,


Peo Sjoblom



"Sheeloo" wrote in message
...
The other option is to Format the cells as Custom-00000. This way nos
will
be stored as numbers though I can't think of any use.

"Orrie" wrote:

Thanks Peo, it worked, although I had to try it twice until it did. The
first time, there was a little error icon next to the cell. The error was
supposed to be an apostrophe preceding the numbers. There was no
apostrophe,
but I deleted the entry, retyped it and then selected the setting in
Format
Cell to accept the entry with the "error," the entry worked.

Orrie

"Peo Sjoblom" wrote in message
...
Format the cell as text before entering

--


Regards,


Peo Sjoblom

"Orrie" wrote in message
...
Hi,

I am entering addresses into an Excel worksheet the I need to use in a
mail merge. One of the addresses has a five digit postal zip code that
begins with a "0" (zero.) I typed it in like any other zip code entry,
but Excel dropped the zero as soon as I moved to the next cell. I
tried
converting the entry into "text," but when I printed the record, only
the
zero printed.

Other zip codes that do not start with zero work fine in a mail merge
and
in printing the records.

How can I enter a zip code that begins with a zero, so it will appear
properly in Excel and in a mail merge?

Thank you.

Orrie











  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 793
Default How to enter zip starting with 0 (zero)

Yeah, you are right. Just pointed this out for future use...

"Peo Sjoblom" wrote:

The OP wants to use it for mailmerge in Word then it doesn't matter how they
are numerically formatted


--

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 113
Default How to enter zip starting with 0 (zero)

I have tried all these suggestions, and nothing is working.

I have a database in Excel and am trying to convert it to Word via mail
merge. (Wouldn't you know, in MA, most zips start with zero??)

Have tried formatting that column as Zip. As text. As text with an
apostrophe. As Custom- 00000.

I'm beginning to feel like I should throw the computer out the window.

Any help would be greatly appreciated!

"Sheeloo" wrote:

Yeah, you are right. Just pointed this out for future use...

"Peo Sjoblom" wrote:

The OP wants to use it for mailmerge in Word then it doesn't matter how they
are numerically formatted


--

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,805
Default How to enter zip starting with 0 (zero)

I have lived in NJ so I know zip code and start with a zero :-)

What do you mean when you say 'converting it to word via mail merge'?

Suppose you have your zip codes in Col A
then enter this in B1 and copy down
=IF(LEN(A2)=4, "0"&A2,""&A2)

This will prefix a 0 to four digit zipcodes and convert them to Text

Next, select Col B, press Ctrl-C to copy, Right-click on B1, chooshe Paste
Special and then select Values, click OK...

You can now use Col B as your zip codes...

You can send your file to me.
To get my id, add @hotmail.com to the end of with
details of what you want to achieve.

"Rachel" wrote:

I have tried all these suggestions, and nothing is working.

I have a database in Excel and am trying to convert it to Word via mail
merge. (Wouldn't you know, in MA, most zips start with zero??)

Have tried formatting that column as Zip. As text. As text with an
apostrophe. As Custom- 00000.

I'm beginning to feel like I should throw the computer out the window.

Any help would be greatly appreciated!

"Sheeloo" wrote:

Yeah, you are right. Just pointed this out for future use...

"Peo Sjoblom" wrote:

The OP wants to use it for mailmerge in Word then it doesn't matter how they
are numerically formatted


--

  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default How to enter zip starting with 0 (zero)

This is the ANSWER! I am in CT and we have zips starting with 0. For years
I have been formatting to text, then reenterin zero's. WHAT A WAISTE OF TIME!
Sheeloo has the answer!!!!!! YIPPPIEEEE! You are a life saver!

"Sheeloo" wrote:

I have lived in NJ so I know zip code and start with a zero :-)

What do you mean when you say 'converting it to word via mail merge'?

Suppose you have your zip codes in Col A
then enter this in B1 and copy down
=IF(LEN(A2)=4, "0"&A2,""&A2)

This will prefix a 0 to four digit zipcodes and convert them to Text

Next, select Col B, press Ctrl-C to copy, Right-click on B1, chooshe Paste
Special and then select Values, click OK...

You can now use Col B as your zip codes...

You can send your file to me.
To get my id, add @hotmail.com to the end of with
details of what you want to achieve.

"Rachel" wrote:

I have tried all these suggestions, and nothing is working.

I have a database in Excel and am trying to convert it to Word via mail
merge. (Wouldn't you know, in MA, most zips start with zero??)

Have tried formatting that column as Zip. As text. As text with an
apostrophe. As Custom- 00000.

I'm beginning to feel like I should throw the computer out the window.

Any help would be greatly appreciated!

"Sheeloo" wrote:

Yeah, you are right. Just pointed this out for future use...

"Peo Sjoblom" wrote:

The OP wants to use it for mailmerge in Word then it doesn't matter how they
are numerically formatted


--



  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,805
Default How to enter zip starting with 0 (zero)

I know the feeling - when you find a new and better way of doing something
you had been doing for years... elation and regret (why did I not know this
earlier feeling)

Thanks for the feedback... We love to hear the cases where someone benefited
from our experience...

"maryc" wrote:

This is the ANSWER! I am in CT and we have zips starting with 0. For years
I have been formatting to text, then reenterin zero's. WHAT A WAISTE OF TIME!
Sheeloo has the answer!!!!!! YIPPPIEEEE! You are a life saver!

"Sheeloo" wrote:

I have lived in NJ so I know zip code and start with a zero :-)

What do you mean when you say 'converting it to word via mail merge'?

Suppose you have your zip codes in Col A
then enter this in B1 and copy down
=IF(LEN(A2)=4, "0"&A2,""&A2)

This will prefix a 0 to four digit zipcodes and convert them to Text

Next, select Col B, press Ctrl-C to copy, Right-click on B1, chooshe Paste
Special and then select Values, click OK...

You can now use Col B as your zip codes...


  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 915
Default How to enter zip starting with 0 (zero)

Sheeloo wrote:
I know the feeling - when you find a new and better way of doing something
you had been doing for years... elation and regret (why did I not know this
earlier feeling)

Thanks for the feedback... We love to hear the cases where someone benefited
from our experience...

"maryc" wrote:

This is the ANSWER! I am in CT and we have zips starting with 0. For years
I have been formatting to text, then reenterin zero's. WHAT A WAISTE OF TIME!
Sheeloo has the answer!!!!!! YIPPPIEEEE! You are a life saver!

"Sheeloo" wrote:

I have lived in NJ so I know zip code and start with a zero :-)

What do you mean when you say 'converting it to word via mail merge'?

Suppose you have your zip codes in Col A
then enter this in B1 and copy down
=IF(LEN(A2)=4, "0"&A2,""&A2)

This will prefix a 0 to four digit zipcodes and convert them to Text

Next, select Col B, press Ctrl-C to copy, Right-click on B1, chooshe Paste
Special and then select Values, click OK...

You can now use Col B as your zip codes...



I love lightbulb moments!

For kicks, here is another way for the OP:

=RIGHT("0000" & A2,5)

Just in case you have 1-digit zip codes (^:, or want to pad any "number"
with zeros on the left. Useful for SSNs, flat-file fields, or anywhere
else you need a string to be a specific length.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enter doesnt return to the cell below the starting point anymore Jeff Excel Discussion (Misc queries) 3 April 26th 08 02:19 AM
Macro to select cells in column enter data then press enter NP New Users to Excel 1 February 20th 08 05:21 PM
How do you enter zip codes starting with 0 for a mail merge J Slocum Setting up and Configuration of Excel 2 August 9th 06 12:44 PM
how do I enter a zipcode starting with a 0 Bea VanKampen Excel Discussion (Misc queries) 2 May 25th 05 12:02 AM
how do i enter numbers starting with zero in excel achitsyerself Excel Discussion (Misc queries) 3 May 12th 05 08:21 PM


All times are GMT +1. The time now is 03:53 PM.

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

About Us

"It's about Microsoft Excel"