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
Results 1 to 9 of 9
Thread: Cell format
-
5 Star Lounger
- Join Date
- Aug 2006
- Posts
- 784
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2010-01-25 20:37
-
Subscribe to get a FREE chapter from Windows 7 The Missing Manual
This month, every Windows Secrets subscriber can download a one-chapter excerpt of Windows 7: The Missing Manual.Windows 7: The Missing Manual provides valuable information to help you overcome these difficulties in learning a new operating system. Subscribe today to download your free excerpt.
-
Platinum Lounger
- Join Date
- Nov 2001
- Location
- Vienna, Wien, Austria
- Posts
- 5,009
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2010-01-26 01:23Format the Cell as Text - using Ctrl+1 to access the Formatting function. HTH
Grüße
-
Super Moderator
- Join Date
- Jun 2002
- Location
- Mt Macedon, Victoria, Australia
- Posts
- 3,993
- Thanks
- 1
- Thanked 44 Times in 43 Posts
2010-01-26 01:43Or Precede the zip code with an apostrophe
'0762Regards
John
-
Super Moderator
- Join Date
- Dec 2000
- Location
- Salt Lake City, Utah, USA
- Posts
- 9,507
- Thanks
- 0
- Thanked 3 Times in 3 Posts
2010-01-26 16:01Excel 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.
Originally Posted by jmt356
-John ... I float in liquid gardens
[acronym title="Gday mate!"]
[/acronym] [acronym title="What up Dude?"]
[/acronym] UTC -7±DS
-
Super Moderator
- Join Date
- May 2002
- Location
- Canberra, Australian Capital Territory, Australia
- Posts
- 3,303
- Thanks
- 0
- Thanked 93 Times in 91 Posts
2010-01-26 16:55Hi John, Originally Posted by JohnBF
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. Originally Posted by jmt356
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-0000Cheers,
Paul Edstein
[MS MVP - Word]
-
5 Star Lounger
- Join Date
- Jan 2001
- Posts
- 1,109
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2010-01-27 10:09Since 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.
-
5 Star Lounger
- Join Date
- Aug 2006
- Posts
- 784
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2010-01-27 20:34When 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?
-
Super Moderator
- Join Date
- May 2002
- Location
- Canberra, Australian Capital Territory, Australia
- Posts
- 3,303
- Thanks
- 0
- Thanked 93 Times in 91 Posts
2010-01-27 20:39Hi JMT, Originally Posted by jmt356
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
[MS MVP - Word]
-
New Lounger
- Join Date
- Jul 2007
- Posts
- 5
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2010-01-28 03:41I 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.
