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!

 

[Jonmo1]

Try this

A1 = original string
B1 =LEFT(A1,LEN(A1)-LEN(C1)-1)
C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[Rick Rothstein]

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.

Posting code for you custom function (whether it works or not) is always useful to orient us for the details you leave out of your description (it allows us to write sample code for you that is applicable to your data layout). Without that, I'll just suggest you look up the InStrRev function which allows you to obtain the location for text by searching for that text from the back of the text rather than the front of the text the way InStr does.

 

[jpfulton]

Try this

A1 = original string
B1 =LEFT(A1,LEN(A1)-LEN(C1)-1)
C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

This is great... Any chance you could help me turn this into a custom function... or maybe 2 separate ones if necessary.

 

[István Hirsch]

As for the current case (if state abbreviations always contain two letters):

B1:

=SUBSTITUTE(A1&"ß"," "&C1&"ß","")

C1:

=RIGHT(A1,2)

 

[Jonmo1]

I'd avoid UDF altogether.
If it CAN be done with built in functions, then it probably should be.

And Istvan noticed something very simple that I didn't.
If the end is always a state abbreviation, 2 characters..
Then it's even simpler...

A1 = original string
B1 =LEFT(A1,LEN(A1)-3) <- minus 3 not 2, because of the space before the state abbreviation.
C1 =RIGHT(A1,2)

 

[jpfulton]

As for the current case (if state abbreviations always contain two letters):

B1:

=SUBSTITUTE(A1&"ß"," "&C1&"ß","")

C1:

=RIGHT(A1,2)

Thank you.

 

[jpfulton]

A1 = original string
B1 =LEFT(A1,LEN(A1)-3) <- minus 3 not 2, because of the space before the state abbreviation.
C1 =RIGHT(A1,2)

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.

 

[Jonmo1]

You're welcome.

 

[István Hirsch]

Thank you.

You are welcome.