How to split a string of text in excel based on a given word

Question

I have a list of combinations of companies, cities, and states in excel, each a string. I would like to split the string of words based on a given word (the city name) and the result to be two columns, one with with company name, one with the city and state.

Splitting on space or symbol delimiters doesn't work because the companies don't all have one word names, and similar for cities.

I have thousands of records and would like to loop this as well. I've tried the SPLIT() function in VBA but not sure how to loop it.

Initial                     Splitting word  Result 1     Result 2
Clean Choc Detroit MI       Detroit         Clean Choc  Detroit MI
Space Kites Des Moines IA   Des Moines      Space Kites Des Moines IA
Tattoosie Chicago IL        Chicago         Tattoosie   Chicago IL
One for Two New York City NYNew York City   One for Two New York City NY
Limonistas Carlsbad CA      Carlsbad        Limonistas  Carlsbad CA