move numbers on street address to new column

This is my first time using this web site so please bear with me.
I have an address spreadsheet. What I would like to be able to do is sort by street name.
Questions:
1) is there a way to select the number of the street and put it in its own column so I can sort by street name?
2) is there a way without taking out the street number and sorting by street name only?
ex;
3138 MESCALERO DR DHLS
3126 MESCALERO DR DHLS
3117 ARTHUR DR DHLS
3119 E ARTHUR DR DHLS
3121 ARTHUR DHLS
3125 E ARTHUR DR DHLS

Originally Posted by mdunstan

This is my first time using this web site so please bear with me.
I have an address spreadsheet. What I would like to be able to do is sort by street name.
Questions:
1) is there a way to select the number of the street and put it in its own column so I can sort by street name?
2) is there a way without taking out the street number and sorting by street name only?
ex;
3138 MESCALERO DR DHLS
3126 MESCALERO DR DHLS
3117 ARTHUR DR DHLS
3119 E ARTHUR DR DHLS
3121 ARTHUR DHLS
3125 E ARTHUR DR DHLS

I suppose you have above street addresses in col A starting from cell A1
put in B1 =MID(A1,1,FIND(" ",A1)-1)*1 and copy down
put in C1 =MID(A1,FIND(" ",A1)+1,100) and copy down

now col B will show numbers which at the begining of the addresses
and col C will show remaining address.
you can sort now accorgind to your preferences.

Regards.

I have attached a file with some of the listings I am working with. Column A has the Track/lot info, B has street address. Column C has the name, D other owner of record, E, mailing address, etcThank you

104-29-060 2450 SEABRING DR LOZANO LEONARD & DEBORAH JT 1920 GLENSTONE AVE HACIENDA HEIGHTS CA 91745
104-29-061 945 HURRICANE DR ARGUELLES ANGEL & SHAUNNA CPWRS 30226 FT CADY RD NEWBERRY SPRINGS CA 92365
104-29-062 2445 BARRANCA DR ERB NICOLA L ETAL JT HENNINGER DWIGHT E JT PO BOX 4983 VAIL CO 81658
104-29-063 2435 BARRANCA DR LHCY MORNINGSTAR MICHAEL & JOHANNA CPWRS 2435 BARRANCA DR LAKE HAVASU CTY AZ 86403
104-29-065 2415 BARRANCA DR REINHARDT DONALD W 5252 EAGLE DALE AVE LOS ANGELES CA 90041
104-29-066 2405 BARRANCA DR RUSSELL ROBERT & GWEN CPWRS 82 N MAIN ST SOUTH SEERFIELD MA 01373
104-29-067 2401 BARRANCA DR LHCY SPENCE LEONARD F 2401 BARRANCA DR LAKE HAVASU CTY AZ 86403
104-29-069 2420 SAN JUAN DR KENDALL JANE A 2420 SAN JUAN DR LAKE HAVASU CTY AZ 86403

Originally Posted by mdunstan

I have attached a file with some of the listings I am working with. Column A has the Track/lot info, B has street address. Column C has the name, D other owner of record, E, mailing address, etcThank you

104-29-060 2450 SEABRING DR LOZANO LEONARD & DEBORAH JT 1920 GLENSTONE AVE HACIENDA HEIGHTS CA 91745
104-29-061 945 HURRICANE DR ARGUELLES ANGEL & SHAUNNA CPWRS 30226 FT CADY RD NEWBERRY SPRINGS CA 92365
104-29-062 2445 BARRANCA DR ERB NICOLA L ETAL JT HENNINGER DWIGHT E JT PO BOX 4983 VAIL CO 81658
104-29-063 2435 BARRANCA DR LHCY MORNINGSTAR MICHAEL & JOHANNA CPWRS 2435 BARRANCA DR LAKE HAVASU CTY AZ 86403
104-29-065 2415 BARRANCA DR REINHARDT DONALD W 5252 EAGLE DALE AVE LOS ANGELES CA 90041
104-29-066 2405 BARRANCA DR RUSSELL ROBERT & GWEN CPWRS 82 N MAIN ST SOUTH SEERFIELD MA 01373
104-29-067 2401 BARRANCA DR LHCY SPENCE LEONARD F 2401 BARRANCA DR LAKE HAVASU CTY AZ 86403
104-29-069 2420 SAN JUAN DR KENDALL JANE A 2420 SAN JUAN DR LAKE HAVASU CTY AZ 86403

select all the columns with your data and go to menu Data > Sort and give the name of column which contains street addresses and sort it.

Starguy, thank you for the formulas, they worked great