MID, LEFT, RIGHT help

  • Thread starter Thread starter mattg
  • Start date Start date
M

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
 
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
 
That worked great but some street names have spaces, "West Main" for example
and they get broken up
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top