Custom Split

Bullflip

New Member
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:

Capture.JPG


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!
 

Some videos you may like

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.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,930
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,930
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,561
Messages
5,548,743
Members
410,868
Latest member
Dhanas
Top