How to sort an address with numbers and text in cell

  • Thread starter Thread starter MICHAELD.
  • Start date Start date
M

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?
 
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.
 
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.





.
Click to expand...
 
Back
Top