# Custom Split

#### Bullflip

##### New Member
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!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### sandy666

##### Well-known Member
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

##### New Member
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.

Replies
3
Views
68
Replies
20
Views
1K
Replies
4
Views
119
Replies
6
Views
102
Replies
0
Views
56