sorting

    Hi.. I have a large list of addresses that are in no particular order and the house # and street name are in one cell.. here's a small sample of column a:


    86 Rosecliff Ln
    153 Westwind Drive
    91 McLAne Lane
    400 Wellington Hill Road
    30 Garden Walk Drive
    7 Rosewood Lane
    559 Megan Drive


    the total list is about 1500 rows or so... what is the easiest way I can go about sorting this by street name then #, as opposed to the #'s which the regular sort does? Thanks for any help provided!

    Sorting


    Hi jjst34,


    First thing you need to do is to select your entire range(A1:A1500). Next go to Data>Text to Columns. Follow the prompts and you should be able to seperate the text by spaces. After that, you should be able to sort as you wish.


    Hope it helps,


    Ed

    Or, assumining your data are in column A and start in cell A2, put the following formula in B2 (insert a new column if needed) and drag down to the end of your data.
    =MID(A2,FIND(" ",A2)+1,100)
    Then copy the data in column B and paste special as values.
    Finally sort on column B as the first key and A as the second.
    To be on the safe side, in case there are extra blanks you could use the formula
    =TRIM(MID(TRIM(A2),FIND(" ",TRIM(A2))+1,100))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login