sort in excel an address by street??

[jackwitt]

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

 

[Damon Ostrander]

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

 

[jackwitt]

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)

 

[barkley]

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

 

[jackwitt]

Barkley, I really appreciate, Thanks!!

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


Thanks all,