Results 1 to 9 of 9

Thread: Cell format

  1. #1
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Format the Cell as Text - using Ctrl+1 to access the Formatting function. HTH
    Gre

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Or Precede the zip code with an apostrophe

    '0762
    Regards
    John



  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by jmt356 View Post
    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.
    -John ... I float in liquid gardens
    UTC -7DS

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by JohnBF View Post
    Quote Originally Posted by jmt356 View Post
    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.
    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
    [MS MVP - Word]

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by jmt356 View Post
    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
    [MS MVP - Word]

  9. #9
    New Lounger
    Join Date
    Jul 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •