separate an address street/city,state,zip within a cell?

[Guest]

This should not be a hard thing to do, but I can't get it. the city will
always be the same with only 2 different zips.

Example: 1957 NE 6 ST DEERFIELD BEACH, FL 33441
2430 DEER CREEK C C BLVD APT 702-11 DEERFIELD BEACH, FL 33442

looking to extract "DEERFIELD BEACH, FL 33441" and move to the blank cell to
the right?

I want to create labels. Help would be greatly appreciated.

 

[Meebers]

The way I always have to do it is to start with a *.txt file. Open excel
and point to that txt file, choose delimited and your delimiter (coma,space
or what ever) and finish the wizard. This will seperate everything, you can
combine if you want with a simple =a1&b1 etc. copy/paste special/value and
put it where you want.

 

[Tiro]

I'm puzzled too. Is that example in one cell or spread over multiple cells?

 

[MartinW]

Hi Puzzled,

If your data remains exactly like your example then all you need
is =RIGHT(A1,25) in B1 and drag down as far as needed.
(that's assuming your data is in A1 down)

If your data is likely to change slightly such as
DEERFIELD BCH, FL 33441
DEERFIELD BCH, FLORIDA 33441
etc.
Then you will need a slightly different approach.

HTH
Martin

 

[Guest]

Try this:
=MID(A1,FIND("DEERFIELD",A1),99)

 

[Rick Rothstein \(MVP - VB\)]

This should not be a hard thing to do, but I can't get it. the city will

always be the same with only 2 different zips.

Example: 1957 NE 6 ST DEERFIELD BEACH, FL 33441
2430 DEER CREEK C C BLVD APT 702-11 DEERFIELD BEACH, FL 33442

looking to extract "DEERFIELD BEACH, FL 33441" and move to the blank cell
to
the right?

I want to create labels. Help would be greatly appreciated.

I think you will need to use two columns in order to make your labels from.
Taking you at your word that the city part will always be the same except
for the numbers making up the zip code, and assuming your first address is
in B2 (I presume the name is in column A and you have headers), put these in
the next two columns...

C2: =LEFT(B2,LEN(B2)-26)

D2: =RIGHT(B2,25)

and copy down.

Rick

 

[Guest]

Thank You! Thank You Thank You! It Worked!

You are a genious!!!