I have an excel file which contains one column of information - Zipcode City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string - export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff

Select your range | go to Data | Text to Columns | use Space as delimiter.

hope that helps


"Jeff @ CI" wrote in message
...
I have an excel file which contains one column of information - Zipcode
City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string -
export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff

Yes that helps. It dramatically reduces the number of cells that I will need
to re-work.

Thanks!!!





"Gaurav" wrote:

Select your range | go to Data | Text to Columns | use Space as delimiter.

hope that helps


"Jeff @ CI" wrote in message
...
I have an excel file which contains one column of information - Zipcode
City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string -
export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff

If you go to the toolbar and choose Data, Text to Columns this will seperate
the columns


"Jeff @ CI" wrote:

I have an excel file which contains one column of information - Zipcode City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string - export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff

On Wed, 24 Sep 2008 08:50:16 -0700, Jeff @ CI
wrote:

I have an excel file which contains one column of information - Zipcode City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string - export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff


Area Code:

=LEFT(A1,FIND(" ",A1)-1)

City:

=MID(A1,FIND(" ",A1)+1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

Zip:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
--ron

Ron - worked BEAUTIFULLY!!!!!!!!!!!!!!!!!!!

Thanks!!!

"Ron Rosenfeld" wrote:

On Wed, 24 Sep 2008 08:50:16 -0700, Jeff @ CI
wrote:

I have an excel file which contains one column of information - Zipcode City
AreaCode (12345 Somecity 678) - all in one string per record. I need to
break the string into three columns. Given that a city can have one, two,
three or more words in its name, I am not sure how to simply have a macro
edit the string by inserting a comma after each part of the string - export
to a text file and then import as a csv. I really don't want to manually
edit all 30,000 strings if I can find a way to get around it.

Any ideas?

TIA

Jeff


Area Code:

=LEFT(A1,FIND(" ",A1)-1)

City:

=MID(A1,FIND(" ",A1)+1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

Zip:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
--ron

On Wed, 24 Sep 2008 15:02:10 -0700, Jeff @ CI
wrote:

Ron - worked BEAUTIFULLY!!!!!!!!!!!!!!!!!!!

Thanks!!!

Glad to help. Thanks for the feedback.
--ron