modifying a string

  • Thread starter Thread starter Jeff @ CI
  • Start date Start date
J

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
 
Select your range | go to Data | Text to Columns | use Space as delimiter.

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

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


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