Text to Column

Hello Everyone.

I'm trying to split data into 2 columns.  The original data is made up of cities and states:

Notice that some items have a comma and others don't.  Also, the cities states are different lengths.  So, I cannot use a space or a comma as a delimiter.  I'm trying to figure out how to separate them.  I came up with this formula to add a colon so I can use the delimiter to separate them.

The problem then becomes the fact that I have to count the number of spaces in each city to add the colon.  Then, I have to copy and paste special as values.  Also, notice that in New Orleans (for example), it keeps the comma:

One more issue, if I get a new report with added cities and states, then I have to re-do the calculations.

HELP!!!!

Balta