Sorting addresses

[amab19]

Is there a way to sort a column of addresses (ex. 123 Main St) just by
the street name? If not, is there a way to get rid of all numbers in a
cell, and just leave the street name?

Thanks from a Newbie!

 

[Guest]

If you have a column containing data with a number (arbitrary # of digits)
followed by a blank followed by the street name, then select the column and
pull-down:

Data > Text to Columns... and select delimited and use the space as the
delimiter. This will cause the address to be split into at least two column.
The street name will be in the second column.

 

[amab19]

Thank you. Just what I needed.

 

[David McRitchie]

You might find it easier to run a macro to split off after the first space
character. Then you won't have to recombine an unknown number of
columns before the remainder of your address that you had to stick in.

http://www.mvps.org/dmcritchie/excel/join.htm#septerm
so you would know that you have to insert exactly one column before
running the macro.

Or if you don't want to separate the street number permanently you could
use sort on a helper column instead. The worksheet solution is also
included in the above link, but here is an example.
J2: =MID(C2,FIND(" ",C2)+1,LEN(C2)-FIND(" ",C2))