Replace Last Space with Another Character

[TheDave]

In my cells I've got cities and states, with the one-word states being actually written out:

Aberdeen SD
North Platte Nebraska

I'd like to end up with a column of the cities and a column of the states. If I just do text to columns though, the cities with more than one word throw everything off. I can't just pull the last two characters over either, because some of the states are written out.

How can I replace the final space in the cell with, say, a semicolon? Then I can do text to columns and tell it to look for the semicolon divider, rather than the space.

I was originally trying to do something a little different if you want a greater challenge. I'd really prefer this:

Column A currently has something like:
Aberdeen South Dakota
New York New York
Fremont Nebraska

Again, I just want the state pulled over into the second column. So I'd love a formula that replaces the second to last space with a semicolon IF one of the two-word states is contained in that text (there are ten of those states--so Aberdeen South Dakota would become Aberdeen;South Dakota), or else it replaces the last space with a semicolon (so Fremont Nebraska would become Fremont;Nebraska).

I just figured it would be easier if I did a global find and replace on all the two-word states first because there are only ten of them, then pull the state into a second column. Since I haven't had much luck with that though, I'm open to the original plan if you can figure out a way to do it. Also, that would leave a couple cities abbreviated (like New York New York becomes NY NY), which isn't the end of the world, just not preferable either.

Thanks for your help.

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	Aberdeen SD	Aberdeen	SD
2	North Platte Nebraska	North Platte	Nebraska
Sheet4

Edit: that won't work for all your examples.

 

[TheDave]

Worked great, thanks!