Separate City, State

  • Thread starter Thread starter Bridget Stacy
  • Start date Start date
B

Bridget Stacy

I have a column in a spreadsheet that contains the city
and the state in the following format:

city, ST

I would like to separate these into two separate columns.
Is there a function that will do that? Some of the cities
are more than one word (for example Los Angeles). All of
the states are in their 2 letter abbreviation.
Thanks!
 
Hi

If there is a comma in between, like your example, you can do it using Data
/ Text to Columns with the Delimited option.
 
Assuming that your data is in column A, try:

Put this formula in B1 and copy down,
=LEFT(A1,FIND(",",A1)-1)

Put this formula in C1 and copy down,
=RIGHT(A1,LEN(A1)-(FIND(",",A1)+1))

You can then "copy > edit > paste special > values" for each of the two new
columns and delete column A.

Hope this helps!
 
Back
Top