I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?

if you have commas or other separators in the cells than use the text to
columns function in the data menu - it is a wizard function that will guide
you though the steps

"corkypine" wrote:

I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?

Unfortunately I don't

The addresses are as follows:
330 6th Avenue North, Suite 200 Southport, MS 98227

All of that is in one cell...Any other suggestions?



"Kentle" wrote:

if you have commas or other separators in the cells than use the text to
columns function in the data menu - it is a wizard function that will guide
you though the steps

"corkypine" wrote:

I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?

It looks like you have double (or greater) spaces between the data. If that
is a rule, you can replace the string " " (two spaces) with a symbol ("|",
for example), and make the text to columns using that symbol.

Hope this helps,
Miguel.

"corkypine" wrote:

Unfortunately I don't

The addresses are as follows:
330 6th Avenue North, Suite 200 Southport, MS 98227

All of that is in one cell...Any other suggestions?



"Kentle" wrote:

if you have commas or other separators in the cells than use the text to
columns function in the data menu - it is a wizard function that will guide
you though the steps

"corkypine" wrote:

I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?

i took your example address and tried it using the delimited option in the
text to columns - still an issue with the suite number and state zip code
separation - however, if you initally split them all using the comma
separator and first, you could go back and then split the suites and zip code
columns again using a space as the separator as opposed to a comma

"corkypine" wrote:

Unfortunately I don't

The addresses are as follows:
330 6th Avenue North, Suite 200 Southport, MS 98227

All of that is in one cell...Any other suggestions?



"Kentle" wrote:

if you have commas or other separators in the cells than use the text to
columns function in the data menu - it is a wizard function that will guide
you though the steps

"corkypine" wrote:

I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?

I will try that...I was thinking of opening it as a deliminated file and
putting the commas in that way - it may be easier than individually - The
biggest problem is that there is no standard, in some cases there are
multiple spaces between each and in other cases there is one or none...I
guess I will just have to play with it a bit more...

I will try your suggestions!
THANK YOU !!!

"Kentle" wrote:

i took your example address and tried it using the delimited option in the
text to columns - still an issue with the suite number and state zip code
separation - however, if you initally split them all using the comma
separator and first, you could go back and then split the suites and zip code
columns again using a space as the separator as opposed to a comma

"corkypine" wrote:

Unfortunately I don't

The addresses are as follows:
330 6th Avenue North, Suite 200 Southport, MS 98227

All of that is in one cell...Any other suggestions?



"Kentle" wrote:

if you have commas or other separators in the cells than use the text to
columns function in the data menu - it is a wizard function that will guide
you though the steps

"corkypine" wrote:

I have about 2000 addresses that need to be separated.

In one cell the street address, city, state and zip are all locatated in one
cell. Is there a way that I can separate this so that the street address,
city, state, and zip all have a separate cell?