Parsing Out City and State Formula

I'm trying to create a formula that will parse out a field that comes with the city and state combined to two distinct "City" and "State" fields. Currently I'm using two different formulas, one for pulling out the city and the other for pulling out the state. The one for city is: =LEFT(H5,FIND(" ",H5)-1) and the one for state is: =MID(SUBSTITUTE(H2," ","^",LEN(H2)-LEN(SUBSTITUTE(H2," ",""))),FIND("^",SUBSTITUTE(H2," ","^",LEN(H2)-LEN(SUBSTITUTE(H2," ",""))))+1,256)

The one for state works fine but the one for city doesn't take into consideration if whether or not the city has two names in it. For example, a field containing "CEDAR CREEK NE" parses out to "CREEK" using my current formula. How do I make it recognize when the city is two words and to either parse out one or two, depending on the length?

Any help is GREATLY appreciated!

Cheers!

Attached is an example of what I'm trying to accomplish City State Parsing Example.xlsx

I apologize, I've attached a new example.City State Parsing Example2.xlsx

Works like a charm, you're the BEST! Thank you kindly