Sorting addresses

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

amab19 - 18 Dec 2005 09:38 GMT

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!

Signature

amab19

Reply to this Message

Gary''s Student - 18 Dec 2005 10:43 GMT

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.

Signature

Gary's Student

> 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!

Reply to this Message

amab19 - 18 Dec 2005 16:04 GMT

Thank you. Just what I needed.

Signature

amab19

Reply to this Message

David McRitchie - 19 Dec 2005 02:48 GMT

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))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> 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
[quoted text clipped - 9 lines]
> >
> > Thanks from a Newbie!

Reply to this Message