sorting residential addresses

[tim]

I have a list of about 100 addresses. I have them in
Excel. I can sort the list by name, or phone number but
when I try to sort the addresses it comes out with the
numerical entry first. Here is and example:
11552 Green Dr
11555 Brown Dr
11556 Green Dr
11557 Blue Dr
But I want it to look like this:
11557 Blue Dr
11555 Brown Dr
11552 Green Dr
11556 Green Dr
In other words I want it to sort by the Name of the Stree
First and then the lower numerical address number.
Is there any way to do this? Thanks.

 

[Don Guillett]

AFAIK you need to break down to 123 street with data>text to columns or use
a dummy column

=MID(A1,FIND(" ",A1,1),LEN(A1))

 

[Alan Beban]

I have a list of about 100 addresses. I have them in
Excel. I can sort the list by name, or phone number but
when I try to sort the addresses it comes out with the
numerical entry first. Here is and example:
11552 Green Dr
11555 Brown Dr
11556 Green Dr
11557 Blue Dr
But I want it to look like this:
11557 Blue Dr
11555 Brown Dr
11552 Green Dr
11556 Green Dr
In other words I want it to sort by the Name of the Stree
First and then the lower numerical address number.
Is there any way to do this? Thanks.

Assuming your data is in Columns A:C, Select the data, click on
Data|Sort|Column B Ascending| Column A Ascending

Alan Beban

 

[David McRitchie]

Hi Tim,
Text to columns is going to break up
10001 west apple orchard way
into 5 columns

You can use a macro for what I think is the best, if you want
a permanent change.
solution http://www.mvps.org/dmcritchie/excel/join.htm#septerm

Without a macro you can add two columns, use a worksheet solution
and keep the formulas or delete them afterwards.
=LEFT(A3,FIND(" ",A3)-1)
=MID(A3,FIND(" ",A3)+1,LEN(A3)-FIND(" ",A3))

 

[Gord Dibben]

If the data shown in your example is all in one cell, you could break the
addresses into two columns and sort on the street names.

Data>Text to Columns could break them into two columns or you could use a
helper column and enter a formula such as......

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

Will put Green Dr in the helper cell.

Gord Dibben Excel MVP

 

[VENKAT]

are these in one column. in that case data-texttocolumns and use delimer
as <spacae>. the data will go into three coumns.
then you can sort one by one column first <street>ascending and then by
no. coumn

 

[David McRitchie]

US Street address are not limited to a number
a streeetname and type of street so text to columns
will not work. Valid example data (test data) that shows different
cases is important when asking a question. But it is up to the
user to implement a correct solution, we can only guess and
suggest.

i.e.
900 Boulevard
41 Washington Ave
104 Old Stage Rd
1024 Route 30