Just better hope there are no addresses in the OP's data that look like this
street address...

http://www.mapquest.com/maps/map.adp...searchtab=home

The following code will handle that problem, but could fail if there is a
town named, say, MT. VERNON... so the code for the OP to use depends on his
knowledge of the structure of his data.

Sub SplitUPPERStringfromEnd()
Dim X As Long, intRow As Long, R As Range
intRow = 1
Do While Range("A" & intRow) < ""
Set R = Range("A" & intRow)
For X = 1 To Len(R.Value)
If Mid(R.Value, X, 4) Like " [A-Z][A-Z][A-Z]*" Then
R.Offset(, 1).Value = Left(R.Value, X - 1)
R.Offset(, 2).Value = Mid(R.Value, X + 1)
Exit For
End If
Next
intRow = intRow + 1
Loop
End Sub

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Please find the below macro to split this. Col A (1 to n) will have your
data. The macro will split the contents to ColB and ColC. Please try and
feedback

Sub SplitUPPERStringfromEnd()
Dim intRow, intTemp, strData
intRow = 1

Do While Range("A" & intRow) < ""
strData = StrReverse(Trim(Range("A" & intRow)))
For intTemp = 1 To Len(strData)
If Asc(Mid(strData, intTemp, 1)) 96 And Asc(Mid(strData, intTemp, 1))
< 123 Then
Range("B" & intRow) = Trim(StrReverse(Mid(strData, intTemp)))
Range("C" & intRow) = Trim(StrReverse(Left(strData, intTemp - 1)))
Exit For
End If
Next
intRow = intRow + 1
Loop

End Sub


If this post helps click Yes
---------------
Jacob Skaria