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

Hi,

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


Mike



"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

Since you street names might be made up of more than one word, you can't use
Excel's Text-To-Columns feature; however, these formulas should work...

D2: =LEFT(A2,FIND(" ",A2)-1)

E2: =MID(A2,LEN(D2)+2,LEN(F2)+LEN(D2)-1)

F2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"mattg" wrote in message
...
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


"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

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

"Mike H" wrote:

Hi,

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


Mike



"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

The formulas for columns D and F are fine but the formula for column E only
returns only 3 or 4 charatcers of the street name.

"Rick Rothstein" wrote:

Since you street names might be made up of more than one word, you can't use
Excel's Text-To-Columns feature; however, these formulas should work...

D2: =LEFT(A2,FIND(" ",A2)-1)

E2: =MID(A2,LEN(D2)+2,LEN(F2)+LEN(D2)-1)

F2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"mattg" wrote in message
...
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

Whoops... I forgot the length of the original text. Try this...

E2: =MID(A1,LEN(D1)+2,LEN(A1)-LEN(F1)-LEN(D1)-2)

--
Rick (MVP - Excel)


"mattg" wrote in message
...
The formulas for columns D and F are fine but the formula for column E
only
returns only 3 or 4 charatcers of the street name.

"Rick Rothstein" wrote:

Since you street names might be made up of more than one word, you can't
use
Excel's Text-To-Columns feature; however, these formulas should work...

D2: =LEFT(A2,FIND(" ",A2)-1)

E2: =MID(A2,LEN(D2)+2,LEN(F2)+LEN(D2)-1)

F2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"mattg" wrote in message
...
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

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

PERFECT !!!

Thanks Rick

"Rick Rothstein" wrote:

Whoops... I forgot the length of the original text. Try this...

E2: =MID(A1,LEN(D1)+2,LEN(A1)-LEN(F1)-LEN(D1)-2)

--
Rick (MVP - Excel)


"mattg" wrote in message
...
The formulas for columns D and F are fine but the formula for column E
only
returns only 3 or 4 charatcers of the street name.

"Rick Rothstein" wrote:

Since you street names might be made up of more than one word, you can't
use
Excel's Text-To-Columns feature; however, these formulas should work...

D2: =LEFT(A2,FIND(" ",A2)-1)

E2: =MID(A2,LEN(D2)+2,LEN(F2)+LEN(D2)-1)

F2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"mattg" wrote in message
...
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

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