Splitting City-State in Excel

If you have data in the following format:

	A	B
1	Cedar Rapids, IA
2	Des Moines, IA
3	San Diego, CA
4	Chicago, IL

You can split the city and state by taking advantage of the comma that has been placed in between the two. You can use Text to Columns.

1. Select the entire column A (click the A).
2. Navigate to Data > Text-to-Columns. This will open a dialogue box. Select the Delimited option and click next.
3. The next step allows you to select a delimiter. The delimiter is a character used to separate data. In our case it is a comma. Select Comma as your delimiter.
4. You should see a preview of the data below within the wizard. Note that Text to Columns will replace adjacent columns so you should be sure to add columns prior to doing so to prevent the contents of adjacent (in this case, column B) contents from being replaced. Click Next
5. For our purposes, the final step of the wizard is unimportant. You can click Finish to complete the operation.

What if City and State aren't separated by a comma?

It is still possible to separate City and State even if they aren't separated by a delimiter. You can use the left(), Len() and right() functions.

	A	B	C
1	Cedar Rapids IA	Cedar Rapids	IA
2	Cedar Rapids IA	=LEFT(A2,LEN(A2)-3)	=RIGHT(A2,2)

The Left() and Right() functions take the leftward and rightward characters specified. For example, =Right(A2, 2) is telling Excel to grab the two rightmost characters from cell A2, which in this case is "Cedar Rapids IA". This function can be used to grab all of the state abbreviations.
What about making a column for just the cities? The key to this is realizing that there are 3 characters following each City in column A: A space and the two letters in a state abbreviation.
Because of this we can use the Len() function to count the number of characters in column A and subtract three from it. Then we can use Left to grab this many leftward characters. This is the formula you see in B2.