Using text to columns to separate city/state

[Hulkerino]

I have a set of data that looks like this:

Milwaukee WI
New Berlin WI
Franklin WI
New York City NY

How can I separate these into 2 columns for city and state without using either a right/trim formula combination or a space delimited/concatenate combination?

 

[oldbrewer]

take the length of each cell, 12 for first one, deduct 2 = 10, add 1 = 11 and use =mid(a1,11,2)

it will actually be

=mid(a1,len(a1)-1,2)