modifying a string

[Jeff @ CI]

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

 

[Gaurav]

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

hope that helps

 

[Jeff @ CI]

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

Thanks!!!

 

[Dee]

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

 

[Ron Rosenfeld]

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

 

[Jeff @ CI]

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

Thanks!!!

 

[Ron Rosenfeld]

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

Thanks!!!

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