MID, LEFT, RIGHT help

[mattg]

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

 

[Mike H]

Hi,

Use Data|Text to columns with a space as the delimeter


Mike

 

[Teethless mama]

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]

That worked great but some street names have spaces, "West Main" for example
and they get broken up

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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