address sorting problem?

[fmb]

I have a mailing list with the standard information; company, address,
city etc. lots of cities and streets
What I want to do is sort the names (within city) by address, but by
street name before number,

I want to wind up with:
1 adam st
2 adam st
5 adam st
4 brown st
Not brown in the middle of adam

Is there a way to "strip" a number portion off of the address column
and create a new "#" column? then I could sort by "street"/"#".
Thanks,
Frank

 

[JulieD]

Hi

yes, you can 'strip' the number portion off using two helper columns, say
your addresses are in column A then in column B type
=LEFT(A1,SEARCH(" ",A1)-1)
this will return everything to the left of the first space in the address
and in column C
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))
this will return everthing to the right of the first space in the address

then fill down (move cursor over bottom of cell B1 until you see + then
double click on it) - repeat for column C

then if you select colums B & C choose copy & then edit / paste special -
values you will replace the formulas with the values and then you can sort
etc on these columns.

alternatively, you can insert some blank columns and play around with data /
text to columns using the space as the deliminator ... however, in your case
i think the above is probably quicker & easier and you don't have to
concatenate the columns to get them back into one field.

Hope this helps
Cheers
JulieD