sort in excel an address by street??

Thanks:  0
Likes:  0

1. ## sort in excel an address by street??

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

2. ## Re: sort in excel an address by street??

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

3. ## Re: sort in excel an address by street??

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)

4. ## Re: sort in excel an address by street??

Add this formula to a spare column and sort:

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

This is assuming that you are searching for the first space in the cell.

HTH

5. ## Re: sort in excel an address by street??

Barkley, I really appreciate, Thanks!!

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

Thanks all,

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•