Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter doesnt return to the cell below the starting point anymore | Excel Discussion (Misc queries) | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
How do you enter zip codes starting with 0 for a mail merge | Setting up and Configuration of Excel | |||
how do I enter a zipcode starting with a 0 | Excel Discussion (Misc queries) | |||
how do i enter numbers starting with zero in excel | Excel Discussion (Misc queries) |