Extract State Code from Single Cell

**maacmaac** · 10-11-2013, 09:36 AM

I have a number addresses; each in a single cell. I need to extract the state code from each cell. Every address is formated exactly the same (i.e. the state code occurs before the last space of each address field). Examples are as follows:

55555 DON JUAN PLACE WOODLAND HILLS CA 91364-6554
55555 POINTE REGAL CIRCLE APT 4X4 DEL RAY BEACH FL 33484
12345 SOUTH LAKESHORE DR APT PH0 BROWNS MILLS NJ 08015-4300
55555 HASSOP LANE BURLESON TX 76028

Desired output as follows:

CA
FL
NJ
TX

Thanks in advance for any comments.

**Tony Valko** · 10-11-2013, 09:45 AM

Try this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),198),99))

**TheCman81** · 10-11-2013, 09:46 AM

One way is this

=LEFT(REPLACE(A1,1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),""),2)

**XOR LX** · 10-11-2013, 09:48 AM

Hi,

Perhaps:

=TRIM(MID(A1,1+FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),2))

Regards

**Fotis1991** · 10-11-2013, 09:52 AM

You can use.

Formula:

Please Login or Register  to view this content.

**maacmaac** · 10-11-2013, 10:08 AM

All solutions worked with exception of last one. Fotis, I'm getting an error on formula. It appears the error is occuring on 'Find' portion of the formula. Thanks.