Custom Split

[Bullflip]

Hi,

I am trying to figure out a way to split off the state abbreviation into a separate column. Currently, it's mixed with the street address in one column. Please see below:

I want to separate the state abbreviations in the state column so I'm looking to split off the first letters from the right (or last 2 letters from the left depending on how you view it). For example, the first row that has "22 E. Claiborne StreetAL36461", it should be able to split off "AL".

Any help would be greatly appreciated. Thanks!

 

[sandy666]

use XL2BB to post your example

 

[Joe4]

Assuming that your first entry is in cell B2, put this formula in C2 and copy down:
=IF(ISNUMBER(MID(B2,LEN(B2)-5,1)+0),MID(B2,LEN(B2)-10,2),MID(B2,LEN(B2)-6,2))

 

[Bullflip]

Assuming that your first entry is in cell B2, put this formula in C2 and copy down:
=IF(ISNUMBER(MID(B2,LEN(B2)-5,1)+0),MID(B2,LEN(B2)-10,2),MID(B2,LEN(B2)-6,2))

This worked perfectly. If you have some time, could you please walk me through the formula? Would like to have a complete understanding of what it is doing logically. Thank you again!

 

[Joe4]

Take it apart bit-by-bit, and you'll be able to see what it does.

So, first try this part:
=MID(B2,LEN(B2)-5,1)

And then this:
=MID(B2,LEN(B2)-10,2)

and this:
=MID(B2,LEN(B2)-6,2)

Once you see what those three parts are doing, it should make more sense.

If there is some part still puzzling you after that, please post back and let me know.