Split City/State From A String.

[Cubist]

Hi, I'm trying to split City/State from strings in column (A) into (B) & (C) like below. The state is always the last 2 letters before the numbers. The rest of it is the city.

String (A)	City (B)	State (C)
SPRINGFIELD MA 010	SPRINGFIELD	MA
CAPE COD MA 025	CAPE COD	MA
WHITE RIV JCT VT 035	WHTIE RIV JCT	VT
PORTSMOUTH NH 038	PORTSMOUTH	NH

 

[Joe4]

Are the number always exactly 3 digits long?

If so, for an entry in cell A2, put this in cell C2:

=LEFT(RIGHT(A2,6),2)

and this in cell B2:

=TEXTBEFORE(A2," " &C2&" ")

 

[Cubist]

Are the number always exactly 3 digits long?

If so, for an entry in cell A2, put this in cell C2:

=LEFT(RIGHT(A2,6),2)

and this in cell B2:

=TEXTBEFORE(A2," " &C2&" ")

Hi Joe,
Sorry for not catching this earlier but I've looked at the dataset again. There's another case where the last part has a U like this...

WHITE RIV JCT VT 035U

This is the source under column A.

L002 3-Digit ZIP Code Prefix Matrix

 

[Joe4]

OK, if we change the formula in cell C2 slightly to this, it should work for both those cases:

=LEFT(TRIM(RIGHT(A2,7)),2)

 

[Scott Huish]

A little shorter for Column C:
=LEFT(TEXTAFTER(A1," ",-2),2)