Results 1 to 5 of 5

sort in excel an address by street??

This is a discussion on sort in excel an address by street?? within the Excel Questions forums, part of the Question Forums category; How can I sort in excel an address by street?? When I sort in excel the addresses column it sorts ...

  1. #1
    New Member
    Join Date
    Sep 2003
    Posts
    3

    Default sort in excel an address by street??

    How can I sort in excel an address by street?? When I sort in excel the addresses column it sorts it by street number only (ex. 111 Bedford will be before 4705 Allen Street).

    Maybe I need to split up the street number from the street in a separate column, but how do I extract the numbers?? (Maybe I have to use the ‘find’ and ‘mid’ function or the 'Sort' and ‘mid’ function , but how exactly is it done?)

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,208

    Default Re: sort in excel an address by street??

    Hello Jack,

    It is not hard to handle addresses that are in the specific form you gave in your example. But what about addresses like:

    345 E. 23rd Ave. (sort this on '23rd'?)

    345 23rd Ave. S.

    612 NE Apple Way (I assume you would want to sort this on Apple, not NE)

    PO Box 2115 (should PO Box 2115 sort before or after P.O. Box 2114?)

    13227 County Highway 7

    One Central Plaza

    Do you have to be able to handle such a wide range of variations? There are dozens of variations of prefixes and suffixes.

    Damon

  3. #3
    New Member
    Join Date
    Sep 2003
    Posts
    3

    Default Re: sort in excel an address by street??

    Thanks for responding Damon.
    Well the answer "for now" is NO. for now I just need to sort it by the word after the first space (i.e. PO Box will be sorted by box)

  4. #4
    Board Regular barkley's Avatar
    Join Date
    Mar 2003
    Location
    UK
    Posts
    148

    Default Re: sort in excel an address by street??

    Add this formula to a spare column and sort:

    =MID(A1,SEARCH(" ",A1,1)+1,LEN(A1))

    Where A1 contains your address line.
    This is assuming that you are searching for the first space in the cell.

    HTH
    Barkley
    Jockeys! - Something Must've Spooked 'Em

  5. #5
    New Member
    Join Date
    Sep 2003
    Posts
    3

    Default Re: sort in excel an address by street??

    Barkley, I really appreciate, Thanks!!

    And the first column would look like this =MID(A1,1,SEARCH(" ",A1,1))


    Thanks all,

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com