+ Reply to Thread
Results 1 to 16 of 16

Excel 2007 : Zipcode sorting problem

  1. 08-09-2010, 07:52 PM #1
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Zipcode sorting problem

    My database has mix of 5 digit zip code and zip+4. Also, after export from address book lost leading "0"s.
    I inserted a column next to the zip code column, formatted it for "text", copy/pasted the zip code field and added the leading "0" manually.
    When I sort, the zip codes that have the leading "0" sort correctly merging the 5 and 9 digit zips appropriately BUT after the "9"s.
    All the others (starting with 2-9) sort 5 digit first then 9 digit.
    Would appreciate help as this is being prepared for a bulk mailing. Thank you!
    Last edited by debrant; 08-20-2010 at 07:03 PM.
    Reply With QuoteRegister To Reply
  2. 08-09-2010, 08:11 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Zipcode sorting problem

    Post an example workbook.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 08-09-2010, 08:49 PM #3
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Here's the file with names etc. stripped out.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 08-09-2010, 10:17 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Zipcode sorting problem

    Try adding a helper column... so in E2 insert formula:

    =IF(LEN(D2)=5,D2&"-0000",D2)

    and copy down all the way.

    Then sort by this new column.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  5. 08-09-2010, 11:22 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Zipcode sorting problem

    I don't see the problem from the example you posted.
    Reply With QuoteRegister To Reply
  6. 08-09-2010, 11:22 PM #6
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Thank you. Is there a way to have a zip code that does not have the +4 not show "-0000" This is for mailing labels so for example 23456-0000 is not okay.
    Reply With QuoteRegister To Reply
  7. 08-09-2010, 11:28 PM #7
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Were you able to do a sort so that it starts with "0" goes thru "9" and the zips with +4 are sorted thruout? For example
    01256
    01256-2000
    01277
    84454
    84666-4444
    93456
    When I sort it, all the 5 digit zips come first then the 9 digit after.
    Thank you.
    Reply With QuoteRegister To Reply
  8. 08-09-2010, 11:54 PM #8
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Zipcode sorting problem

    I had a similar problem a few days ago - drove me crazy!!
    The answer was to do 3 things
    =Text(C2,"00000") first to get the zero leading the 4 digit zips
    Then do an append of nothing to each cell - Like E2 = D2 & ""
    Lastly do a copy and paste of values only over the original column.
    Note - make sure column is formatted as text.

    See attached for what I did.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-10-2010, 07:44 AM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Zipcode sorting problem

    Quote Originally Posted by debrant View Post
    Were you able to do a sort so that it starts with "0" goes thru "9" and the zips with +4 are sorted thruout? For example
    01256
    01256-2000
    01277
    84454
    84666-4444
    93456
    When I sort it, all the 5 digit zips come first then the 9 digit after.
    Thank you.
    This is what I got after sorting and using my formula. You can delete column E after sorting.. your column D is the one you want.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 08-10-2010, 07:58 PM #10
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Quote Originally Posted by NBVC View Post
    This is what I got after sorting and using my formula. You can delete column E after sorting.. your column D is the one you want.
    I understand the part about creating column E, but how do you end up with the values in D that have dropped the "-0000"?

    Do you think this can be simplified so that someone can add contacts and resort as needed? Seems amazing to me that something as common as a zip code sort is such a complicated problem! Thank you!
    Reply With QuoteRegister To Reply
  11. 08-10-2010, 08:01 PM #11
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Quote Originally Posted by MarvinP View Post
    I had a similar problem a few days ago - drove me crazy!!
    The answer was to do 3 things
    =Text(C2,"00000") first to get the zero leading the 4 digit zips
    Then do an append of nothing to each cell - Like E2 = D2 & ""
    Lastly do a copy and paste of values only over the original column.
    Note - make sure column is formatted as text.

    See attached for what I did.
    This looks the most straightforward so far. Do you have a recommendation for an easy way to add names and do a re-sort. It would be done by someone who's even less proficient than I am! Thank you for any ideas.
    Reply With QuoteRegister To Reply
  12. 08-10-2010, 08:03 PM #12
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Zipcode sorting problem

    If you entered zipcodes as numbers, with the 4-digit extension as decimals (e.g., 71006.0213), they would sort naturally.
    Reply With QuoteRegister To Reply
  13. 08-10-2010, 08:12 PM #13
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Quote Originally Posted by shg View Post
    If you entered zipcodes as numbers, with the 4-digit extension as decimals (e.g., 71006.0213), they would sort naturally.
    That makes sense...but then when you print labels, how do you do the "-" instead of the"."?
    Reply With QuoteRegister To Reply
  14. 08-10-2010, 08:53 PM #14
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Zipcode sorting problem

    Maybe with a formula:

    =TEXT(INT(A1), "00000") & IF(MOD(A1, 1), TEXT(10000 * MOD(A1, 1), "-0000"), "")
    Reply With QuoteRegister To Reply
  15. 08-11-2010, 07:43 AM #15
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Zipcode sorting problem

    Quote Originally Posted by debrant View Post
    I understand the part about creating column E, but how do you end up with the values in D that have dropped the "-0000"?

    Do you think this can be simplified so that someone can add contacts and resort as needed? Seems amazing to me that something as common as a zip code sort is such a complicated problem! Thank you!
    I left the D column as you had it, then I created the E column separately, and sorted by that column so that D remained untouched but sorted.
    Reply With QuoteRegister To Reply
  16. 08-20-2010, 07:00 PM #16
    debrant
    debrant is offline
    Registered User
    Join Date
    02-26-2009
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Zipcode sorting problem

    Quote Originally Posted by NBVC View Post
    I left the D column as you had it, then I created the E column separately, and sorted by that column so that D remained untouched but sorted.
    Sorry for late response...This is great thank you so much.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1