sorting question

  • Thread starter Thread starter jjst34
  • Start date Start date
J

jjst34

Hi.. I have a large list of addresses that are in no particular orde
and the house # and street name are in one cell.. here's a small sampl
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
can go about sorting this by _street_name_ then #, as opposed to th
#'s which the regular sort does? Thanks for any help provided
 
I suggest a helper column. If your data is in column A, enter this formula
in column B and copy down:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
This formula will stip the first word (presumably the #) from each address.

Then you can highlight column A and B and sort by B.
 
You would need a temporary help column, assume the addresses are in column A
starting in A2
going down, insert a new column B and in B2 put

=MID(A2,FIND(" ",A2)+1,255)

copy down, copy and paste special as values in place,
select the columns and sort by B ascending, remove B

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top