On Wed, 8 Jul 2009 10:58:01 -0700, 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

Assuming that EVERY address has a house number; and that EVERY address has a
suffix; and that the suffix is a single word or abbreviation at the end of the
string, then the following seems to work:

D2: =LEFT(C2,FIND(" ",C2)-1)
E2: =MID(C2,LEN(D2)+2,LEN(C2)-(LEN(D2)+LEN(F2)+2))
F2: =TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99))

If your address strings are more complex, then the extraction formula will be
also.

Note that the formulas in D2 and F2 must be entered in order for the formula in
E2 to work properly.

This WILL handle streets with compound names.
--ron