Results 1 to 12 of 12
  1. #1
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Zip Code Sort Errors

    I have a sheet with names and address, including a Zip column. There are about 426 names in it. When I click in a cell in the zip column, and click the ascending sort button, the sheet is sorted by zip, but into TWO GROUPS. The first set is about 38 rows, and goes from 28203 to 28105 (why 28105 isn't before 28203, I don't know either). Then it starts over at 28203 and sorts the rest of the rows in ascending order. All of the cells for Zip are "general". I can't figure out what's different about these 38 rows to make them sort above the other 380 or so rows. This screws up label printing in Word for mass mailing. Any ideas on what might be causing this??

    thx.

    -Rich Belthoff

  2. #2
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zip Code Sort Errors

    Zip codes can start with a zero and thus are often entered as text by preceeding the number with an apostrophe (') which keeps the leading zero from being supressed. This would cause the sort order to change. The ' doesn't display in the cell, but if you highlight the cell it will appear in the edit line. Perhaps this is your problem.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Zip Code Sort Errors

    If the values are stored as text, the following procedure will convert to numeric values. In a blank cell enter the value 1. Copy it. Then select the entire range of zip codes and Paste Special and select Multiply. Then sort and see if it makes a difference. You can apply formatting if you need to show leading zeros.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Code Sort Errors

    It sounds like the ZIP codes are entered as text and have a variable number of blanks in front of the first digit. To get it to sort correctly, you can do one of several things. You could convert them to numbers and use a format that displays five digits with leading zeros. Or you could enter them with a single quote in front of the number (that tells Excel to treat the number as text) and the five digit number with leading zeros but no leading blanks.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Code Sort Errors

    Why would that change the sort order?
    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zip Code Sort Errors

    If some are entered as text and some as numbers then the text entries sort after the numbers -- at least they do when I try it. That changes the apparent sort order.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Code Sort Errors

    If you select the whole range, either Format/Cells or the Text to Columns Wizard will format the lot as text, easier than entering with a leading '. The trim function removes leading (and trailing) blanks.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip Code Sort Errors

    Yes, if some are numbers and some are text, then the sort order would not be the way you wanted. I read your message to say that all were entered with the single quote.
    Legare Coleman

  9. #9
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Zip Code Sort Errors

    Thanks, Chuck, but that's not it. All of the zip codes begin with "28". I fixed it by re-typing and then copy/pasting groups of zip codes. There were no leading ' characters or blanks as far as I could tell, and I tried formatting them all as "General", but I guess some were entered as numbers and some as text. That's all I can think of.

    -Rich.

  10. #10
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Zip Code Sort Errors

    I think maybe some were stored as text, and converting all cells to "General" format doesn't seem to fix that. I fixed it by re-typing and then copy/pasting groups of zip codes.

    Thx.

    -Rich.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Zip Code Sort Errors

    Sorry I'm late to the party. What you could have done is convert them to values =value(<source cell>) then format those as Special, Zip Codes. Excel will then sort them correctly.

    (I get zip code censuses a lot, and the formats are so often inconsistent I use this formula to convert them: =VALUE(LEFT(TEXT(<source cell>,"00000"),5).) HTH,
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    constance2u
    Guest

    Re: Zip Code Sort Errors

    I don't have to sort zip codes often enough to set up a routine, but often enough that I've encountered the problem. My quick and dirty fix is to do a search and replace for the ones that give me a problem. I.e., search for "28" replace with "28".

Posting Permissions

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