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
If you want to avoid VBA, the following formulas could work for you:
=LEFT(A2,FIND(B2,A2)-2)
=RIGHT(A2,LEN(A2)-FIND(B2,A2)+1)