Split column on last space

[jpfulton]

I do a lot of data manipulation in Excel. Often I have cases come up where I get a single column of both cities and states with no delimiter. An example of one such field is "Hobe Sound FL". I need that to be separated into 2 columns. One with just "Hobe Sound" and one with just "FL". Other cities of course are only a single word like "Naples FL".

My current solution is to use a custom function that reverses the entire string, then I separate using the first space as the delimiter. Last step is to reverse both columns back to their original form and the job is done... A lot of steps involved and a lot of room for human error. Just looking for a way to automate this a little more.

Any ideas appreciated. Thanks!

 

[István Hirsch]

Wow. This is simple. For the most part this will work all of the time. I frequently get very sloppy data where some states are spelled out and some are abbreviated. Usually the data only includes 1 or a few states so a simple find and replace before applying these functions will do the trick.

Thanks guys.

Either abbreviated or not, you can extract the name of the state (if it is one word) from the end of the string with a „simple find/replace”; just copy col A to col B and in the latter Replace „* „ (asterist space, without quotes) with nothing.

 

[jpfulton]

Either abbreviated or not, you can extract the name of the state (if it is one word) from the end of the string with a „simple find/replace”; just copy col A to col B and in the latter Replace „* „ (asterist space, without quotes) with nothing.

wow, you're right...

I think my plan going forward will be

Col A -- Source data
Col B -- =left(a2,len(a2)-len(c2)-1)
Col B -- find and replace for "* " (asterisk space without quotes)

The other way I apply this type of thing is separating a last name out of a column with names like "John Smith". Looks simple unless you also have names in that column like "Mary Ann Smith" and some also are "Dr. James Smith"

Thanks again all.