How do I get the zeros to show up in the beginning of a number?
 

I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from happening.

For example, 01234 automatically turns into 1234 which won't be the correct
zip code when I mail merge the information.

Use text zips, precede the entry with an apostrophe or pre-format cells as
text and the leading zero will display

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

Regards,

Peo Sjoblom

"cmreisner" wrote:

I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from happening.

For example, 01234 automatically turns into 1234 which won't be the correct
zip code when I mail merge the information.

Hi

two options
once you've typed your postcodes, select them and go into format / cells -
on the numbers tab, click on custom and type 00000 then press OK

or
when you're entering the "numbers" type a ' before them e.g.
'01234

or
before typing the postcodes, select the column where you're going to type
them and choose format / cells / text.

the first method leaves your numbers as numbers (ie you can add them up if
you wish), the second and third methods converts them to text.

Cheers
JulieD

"cmreisner" wrote in message
...
I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from
happening.

For example, 01234 automatically turns into 1234 which won't be the
correct
zip code when I mail merge the information.

I have tried all the options you suggest. On the worksheet the zip code looks
correct, but it's still treated as a number when formatted as a zip code and
will not mail merge as a zip code with a leading zero.

With "Format Cells, Number, Special, Zip Code" for zip code 08070, I get
8070 in the mail merge.

With "Format cells, number, custom, 00000,"" I get 0 in the mail merge
(without any other numbers).

With "Format cells, number, text" with or without a ' in front of the zero,
I get a 0 in the mail merge (again, without any other numbers)

This is really getting frustrating. Everybody knows that there are areas of
the country where zip codes begin with zero. We can't treat every number the
same. Sometimes those leading zeroes are necessary.

"JulieD" wrote:

Hi

two options
once you've typed your postcodes, select them and go into format / cells -
on the numbers tab, click on custom and type 00000 then press OK

or
when you're entering the "numbers" type a ' before them e.g.
'01234

or
before typing the postcodes, select the column where you're going to type
them and choose format / cells / text.

the first method leaves your numbers as numbers (ie you can add them up if
you wish), the second and third methods converts them to text.

Cheers
JulieD

"cmreisner" wrote in message
...
I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from
happening.

For example, 01234 automatically turns into 1234 which won't be the
correct
zip code when I mail merge the information.

In an adjacent column, enter

=TEXT(A1,"00000")

and fill down.

Use that column to sort and print or even replace the original column by
copying the new one and do a Paste Special Values.

Don't forget to make a copy of your workbook first!

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"KK" wrote in message
...
I have tried all the options you suggest. On the worksheet the zip code
looks
correct, but it's still treated as a number when formatted as a zip code
and
will not mail merge as a zip code with a leading zero.

With "Format Cells, Number, Special, Zip Code" for zip code 08070, I get
8070 in the mail merge.

With "Format cells, number, custom, 00000,"" I get 0 in the mail merge
(without any other numbers).

With "Format cells, number, text" with or without a ' in front of the
zero,
I get a 0 in the mail merge (again, without any other numbers)

This is really getting frustrating. Everybody knows that there are areas
of
the country where zip codes begin with zero. We can't treat every number
the
same. Sometimes those leading zeroes are necessary.

"JulieD" wrote:

Hi

two options
once you've typed your postcodes, select them and go into format /
cells -
on the numbers tab, click on custom and type 00000 then press OK

or
when you're entering the "numbers" type a ' before them e.g.
'01234

or
before typing the postcodes, select the column where you're going to type
them and choose format / cells / text.

the first method leaves your numbers as numbers (ie you can add them up
if
you wish), the second and third methods converts them to text.

Cheers
JulieD

"cmreisner" wrote in message
...
I need to have zip codes in a spreadsheet, but when I type in the zip
code,
the zeros at the beginning disappear. How do I prevent this from
happening.

For example, 01234 automatically turns into 1234 which won't be the
correct
zip code when I mail merge the information.

I want to sum the text entries of the numbers, how do I do it, Please.

VSC

"Peo Sjoblom" wrote:

Use text zips, precede the entry with an apostrophe or pre-format cells as
text and the leading zero will display

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

Regards,

Peo Sjoblom

"cmreisner" wrote:

I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from happening.

For example, 01234 automatically turns into 1234 which won't be the correct
zip code when I mail merge the information.

If you need leading zeros, but don't want to change the numbers to text,
you can use a custom format:

Select the cells
Choose FormatCells
On the Number tab, select the Custom category
In the text box, type: 00000
or the number of zeros that you require
Click OK

vsc wrote:
I want to sum the text entries of the numbers, how do I do it, Please.

VSC

"Peo Sjoblom" wrote:


Use text zips, precede the entry with an apostrophe or pre-format cells as
text and the leading zero will display

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

Regards,

Peo Sjoblom

"cmreisner" wrote:


I need to have zip codes in a spreadsheet, but when I type in the zip code,
the zeros at the beginning disappear. How do I prevent this from happening.

For example, 01234 automatically turns into 1234 which won't be the correct
zip code when I mail merge the information.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html