Results 1 to 8 of 8

Thread: sort zip codes

  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Colorado Springs, CO
    Posts
    110
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This is an issue for me. I sort my contact database by zip code so I can make some visits. Then I realize that 80904 and 80904-1234 are in two different sections of the spreadsheet. Does anyone have an idea how to cause that column to sort the way one would expect?

  2. #2
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    break the zip code down into two cells. The Text to Column feature will do this, but will not preserve the orignial.
    The mid formula will work well for you if the zips are consistent.

    =MID(B2,1,5) in one column
    =MID(B2,7,4) in another column

    Sort on the new columns

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can also convert the column to text via format so that all are text values and some are not text and others numbers which will sort each group separately...

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Colorado Springs, CO
    Posts
    110
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The column of zips are imported from a database export.
    So answer one does not work.
    I tried the format procedure and while it may have converted the column to text, it made not diferrence in putting the zip codes together when they had the zip4 extension.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are you sure you did not tell it to sort numbers that look like text as numbers? You want to make sure you sort everything as TEXT. ...

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Colorado Springs, CO
    Posts
    110
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That answer is confusing to me. I'm missing something.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    How are your zip codes saved in your database - in other words, is the 5-digit part in one field and the 4-digit part in another field? Or is the whole 5 or 9 (10 if you put a dash between the parts) character ZIP code stored in a single column? If you export into Excel and specify that each part (or the single field) is a text field, not a numeric field, then the leading zeros will be preserved, and it will sort properly in either case. If you cannot control the export, then you must specify a special format property for those cells and make certain it is treated as text and is formated to show leading zeros.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Colorado Springs, CO
    Posts
    110
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The field in the database program is one field. When i export to excel, the cells with the extra 4 go farther down in the cloum and the main zips stay on top of the column. By the way, my profile says to notify me by email and I am not getting email notifications I don't think.

Posting Permissions

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