help sorting street addresses.

[victorybasket39]

hey all. i have a list of street addresses that i would like to sort, the only problem is i want to sort by the street names, as opposed to the address. However, i can seem to get excel to only sort by the address number. if anyone has any suggestions on what i can do for this, please let me know. thanks. ~Donald~



3577 HIGUERA ST
109 SOUTH ST
409 NORTH STREET
3440 DEBONAIR DR

Just an extremely small sample, incase you were wondering what i was working with. want to sort list by street name (letters) as opposed to numbers.

 

[viper]

If you have the addresses in one cell then it will only sort by the numbers. To sort by the streets, separate them, numbers in column A, street names in column B. Then highlight all the cells ie.A2,B2,A3,b3,a4,b4 then click Data|Sort|then click the drop down and select column B.

HTH

 

[victorybasket39]

i have over 500 addresses, what is the easiest way to seperate JUST the numbers and street name? using the delimiter i can seperate every individual word, but i would rather have the street name in its entirety in one cell. for example, 1001 in column A and North El gato street in Column B.

 

[viper]

using the delimiter set two break lines, one at the end of the numbers and the other one at the end of the longest street name. Double click on the break lines you don't need to remove them.

 

[Aladin Akyurek]

In B1 enter & copy down:

=MID(A1,SEARCH(CHAR(32),A1)+1,LEN(A1))

where A1 houses a full address.

 

[victorybasket39]

thank you all for your help. i greatly appreciate it, as i am now able to acheive what i wasn't able to an hour ago.