Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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. |
#4
![]() |
|||
|
|||
![]()
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. |
#5
![]() |
|||
|
|||
![]()
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. |
#6
![]() |
|||
|
|||
![]()
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. |
#7
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
in excel how can I get the number to show instead to #value in th. | Excel Worksheet Functions | |||
How to show zeros (0) as "slashed" to eliminate confusion with "O. | Excel Discussion (Misc queries) | |||
only include cells beginning with specified character or number | Excel Worksheet Functions | |||
Why does the formula result show a number but the screen shows ze. | Excel Worksheet Functions | |||
How do I convert 300616a to show as a number 303616? | Excel Worksheet Functions |