How to sort an address with numbers and text in cell

[MICHAELD.]

I get an excel file from the State that puts an address in
one cell. I use Excel 2000. The column has addresses in
the following format: 1234 S Main St

When I sort, it sorts by number first, then letters. I
cannot figure out how to do a sort by street name first
and then by number. ie, I want the streets together by
name and then by number.

I tried text to columns, but it separated each part of the
address, which won't work at all.

Ideas, suggestions?

 

[Naraine]

you need to extract the street name from the address. use the following:


Assuming your address is in A1;

In say C1 type the following:

=MID(A1,FIND(" ",A1,1),LEN(A1))

copy down.

then select all of column C, COPY, PASTE SPECIAL, VALUES. SORT THE DATA
USING COUUMN AS THE PRIMARY SORT KEY.

 

[Guest]

I appreciate your help

-----Original Message-----
you need to extract the street name from the address. use the following:


Assuming your address is in A1;

In say C1 type the following:

=MID(A1,FIND(" ",A1,1),LEN(A1))

copy down.

then select all of column C, COPY, PASTE SPECIAL, VALUES. SORT THE DATA
USING COUUMN AS THE PRIMARY SORT KEY.





.