Splitting address ie. #, Street , & other identifiers in a cell

[dflaherty]

I have a large list with addresses in the cell
I want to split the number out of the cell so that I can
produce the list in street order.

I think I did this before and I am clueless to do it again

 

[JMay]

In A1: 3314 Avenham Avenue
In B1: =MID(A1,FIND(" ",A1,1)+1,100)
Then Copy B1 and Paste-Special Values OK
HTH

 

[David McRitchie]

since this is the worksheet functions newsgroup

=LEFT(A3,FIND(" ",A3)-1)
=MID(A3,FIND(" ",A3)+1,LEN(A3)-FIND(" ",A3))

But for a macro see SEPTERM macro in
http://www.mvps.org/dmcritchie/excel/join.htm#septerm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm