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


"mattg" wrote:

Hi,

I addresses in column C, "123 Main St"(quotes not in actual cell) for
example. How can get the house number in column D, the street name in Column
E and the suffix(ST, RD , AVE, etc.) in Column F? The number of characters
can vary with each cell. Some are all uppercase some are not.

I tried this =MID(C2,FIND(" ",C2)+1,FIND(" ",C2)) to capture the street name
but it doesn't work properly.

Any ideas?

Thanks,
Matt