Cell format

Topic

New Reply

I am trying to enter 07652 as a zip code into a spreadsheet, but Excel keeps on deleting the zero and right aligning the number as 7652. How can I keep the zero and keep it left aligned like the other cells?

JMT

Reply | Quote

Replies

Format the Cell as Text – using Ctrl+1 to access the Formatting function. HTH

Reply | Quote

Or Precede the zip code with an apostrophe

‘0762

Reply | Quote

I am trying to enter 07652 as a zip code into a spreadsheet, but Excel keeps on deleting the zero and right aligning the number as 7652. How can I keep the zero and keep it left aligned like the other cells?

Excel has a built-in “special” number format for US Zip codes; in the number format drop-down look for “special”. See if that works. Or don’t recognize any Jersey addresses.

Reply | Quote

I am trying to enter 07652 as a zip code into a spreadsheet, but Excel keeps on deleting the zero and right aligning the number as 7652. How can I keep the zero and keep it left aligned like the other cells?

JMT

Excel has a built-in “special” number format for US Zip codes; in the number format drop-down look for “special”. See if that works. Or don’t recognize any Jersey addresses. [/quote]
Hi John,

Unfortunately, if you use Excel’s Zip+4 format, which might be desirable if the cells have a mix of Zip & Zip+4 codes, the leading 0s will still be stripped from the basic 5-digit Zip codes. To handle both correctly, you can use the following custom format:
[<=99999]00000;00000-0000

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Since it is a zip code and a set number of characters try this:

In a new column put =TEXT(A1,”000000″) and copy down.

Then highlight that column and
EDIT
copy
Paste Special
Choose values

This removes the calculation. Delete the original column.

Reply | Quote

When I format them as Special and then Zip Code, the cells get a little arrow in the upper left of the cells. What’s that mean?

Reply | Quote

When I format them as Special and then Zip Code, the cells get a little arrow in the upper left of the cells. What’s that mean?

Hi JMT,
It’s an alert about an inconsistency with related cells. If you click on one of the cells with the green arrow, you should see an exclamation mark to the right. Hover the mouse over it and you’ll get an explanation. Click on the dropdown next to the exclamation mark and you’ll be given some treatment options.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I had a mistake in the directions above:

Since it is a zip code and a set number of characters try this:

In a new column put =TEXT(A1,”000000″) and copy down.
(A1 is if your zip code is in column A row 1. Choose the column and row that your zip code is in)
Then highlight that column and
EDIT
copy
EDIT
Paste Special
Choose values

This removes the calculation. Delete the original column.

Reply | Quote