On Wed, 8 Jul 2009 11:17:01 -0700, Teethless mama
wrote:

123 Main St
456 No name Ave
555 blah blah blah RD

if your address always in these format then try this:

D1: =LEFT(C1,FIND(" ",C1)-1)
F1: =TRIM(RIGHT(SUBSTITUTE(C1," ",REPT(" ",99)),99))
E1: =TRIM(SUBSTITUTE(SUBSTITUTE(C1,D1,""),F1,""))

select D1:E1 coppy down as far as needed

Unwanted results if either the address number or the suffix is included in the
street name

Try:

12 12th Ave
147 Strong St

etc.
--ron