Convert State Names to State Initials

[miketran]

Hello,

I found the solution below in this forum and it is very close to what I am looking for, but not quite. I have a list of contacts that has a mix of state names and states initials in the STATE column...The script below just creates a new column...I need a VBA script that will go down the list and replace the NAME of the state with the state abbreviation. Some of the state cells have bad data in them, so those cells need to be skipped...

---

Sub GetStateNames()
Const StateNames As String = _ "Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _ "Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _ "Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,Montana,Nebraska," & _ "Nevada,New Hampshire,New Jersey,New Mexico,New York,North Carolina,North Dakota," & _ "Ohio,Oklahoma,Oregon,Pennsylvania,Rhode Island,South Carolina,South Dakota,Tennessee," & _ "Texas,Utah,Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"

Const StateIds As String = _ "AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,MT," & _ "NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"

Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range

vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")

For Each cell In Range("A2:A200")

If cell.Value <> "" Then
cell.Offset(0, 1).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0))

End If
Next cell
End Sub

---

From this:
AL
bad data
Alaska
CA
New Jersey

To This:

AL
bad data
AK
CA
NJ

Any Ideas?

 

[taurean]

Try this modification.

Sub GetStateNames()
Const StateNames As String = _
"Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _
"Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _
"Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,Montana,Nebraska," & _
"Nevada,New Hampshire,New Jersey,New Mexico,New York,North Carolina,North Dakota," & _
"Ohio,Oklahoma,Oregon,Pennsylvania,Rhode Island,South Carolina,South Dakota,Tennessee," & _
"Texas,Utah,Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"

Const StateIds As String = _
"AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,MT," & _
"NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"

Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range

vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")

For i = LBound(vecStateNames) To UBound(vecStateNames)
    'Set the range to suit
    Range("A2:A200").Replace vecStateNames(i), vecStateIds(i)
Next

End Sub