How to enter zip starting with 0 (zero)

[Orrie]

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

 

[Peo Sjoblom]

Format the cell as text before entering

--


Regards,


Peo Sjoblom

 

[Orrie]

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]

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

 

[Sheeloo]

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.

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[Sheeloo]

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

 

[Rachel]

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]

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 (e-mail address removed) with
details of what you want to achieve.

 

[maryc]

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]

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...

 

[smartin]

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...

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.