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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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