how to sort addresses in microsoft excel

I have 2 lists of addresses on one sheet. Most of the addresses are
duplicates and I want to sort them so it puts all the duplicates together.
The streets include the house number, as 1257 Rock St. I can sort them by
street alone but I need to sort by street and the address together, is there
a way to do this? I am using Excel 2000, 9.0.2720.

On Jul 17, 5:43*pm, GAW > wrote:
> I have 2 lists of addresses on one sheet. Most of the addresses are
> duplicates and I want to sort them so it puts all the duplicates together..
> The streets include the house number, as 1257 Rock St. I can sort them by
> street alone but I need to sort by street and the address together, is there
> a way to do this? I am using Excel 2000, * 9.0.2720.

if your address is always in the format of 1257 Rock. St, you can
split it into 2 columns, 1 for the number, 1 for the street
number =MID(I25, 1, SEARCH(" ", I25, 1)-1)
street =MID(I25, SEARCH(" ", I25, 1)+1, LEN(I25))

then use excel to sort by street column first, then by number column

On Jul 17, 11:00*pm, recrit > wrote:
> On Jul 17, 5:43*pm, GAW > wrote:
>
> > I have 2 lists of addresses on one sheet. Most of the addresses are
> > duplicates and I want to sort them so it puts all the duplicates together.
> > The streets include the house number, as 1257 Rock St. I can sort them by
> > street alone but I need to sort by street and the address together, is there
> > a way to do this? I am using Excel 2000, * 9.0.2720.
>
> if your address is always in the format of 1257 Rock. St, you can
> split it into 2 columns, 1 for the number, 1 for the street
> number *=MID(I25, 1, SEARCH(" ", I25, 1)-1)
> street * *=MID(I25, SEARCH(" ", I25, 1)+1, LEN(I25))
>
> then use excel to sort by street column first, then by number column

to clarify, i25 was the cell containing the address in my test case