Suppose GREENVILLE, SC 29607 and HIDALGO, TX 78557 are in
Sheets("Sheet1").Range("A1:A2"). The following extracts the state in
each cell.

Sub FindState()
For iR = 1 To 2
str1 = Sheets("Sheet1").Cells(iR, 1)
MsgBox Mid(str1, Len(str1) - 7, 2)
Next iR
End Sub


Hth,
Merjet