Splitting US states & cities

[sureshpaulchamy1]

Can you anyone help in splitting the US states & cities in the excel sheet. The data is not in the order and anonymous data mixed up with the data. If only city is available i have to add the state field. Already i have built a list of city and state information.

I ll copy some of the sample rows here.

Streamwood, Illinois
HOOKSETT, NH
Mahopac, NY
Ossining, NY
west hartford, CT
Cedar Springs , Mi
Oldtown, Idaho
HOUSTON, TX
NEW YORK
livonia, MI
Livonia, Michigan
Sunny California
San Diego, CA
San Diego, California
Los Angeles, California
COSTA MESA, CA
Duluth, Georgia
Florida
11801
Hicksville, New York
knoxville, TN
Mount Sinai, New York
Tempe, AZ
WISCONSIN CHEESE
Thomaston, Georgia
Merritt Island, Florida
NORTHERN CALIFORNIA
El Segundo, CA
FLORIDA
Florida
Nationwide Distribution Centers

 

[SteveO59L]

Use Data, Text to Columns with a comma as the delimeter

 

[sureshpaulchamy1]

Thanks you sir for your fast reply

Most of the record in the sheet doesn't have commas to split.

Its an unordered data. Need to be ordered