# 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
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))

• I appreciate both of you replying.. Derk your suggestion worked perfectly.. Thanks a lot!

## Participate now!

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