Fix State Names
Changing State Names To Abbreviations in Excel using VBA
I used to use this code when I needed to format a list of addresses sent to me in an Excel spreadsheet, usually from a client.
Everyone seems to format state abbreviations differently; some people use the whole word, others use the two-letter abbreviation. Personally I prefer the abbreviation, since most of the other programs I work with also use the same format. For those times when a pesky client sends you the entire state name, this code changes it back.
First we set an object reference to a Range object. The code is made more efficient by setting a reference to the selected cells that contain text constants only, so we don't accidently damage a cell with a formula or number. If the length of the cell is greater than 2, we use a Select Case statement to determine the state and change to its abbreviation as appropriate. This is about as basic and simple as it gets.
You are definitely not going to want this code to work on the entire worksheet, so I didn't code it to allow for that type of selection.
Dim rng As Excel.Range
Dim cell As Excel.Range
' use the 2 argument to specify text constants only, not numbers
Set rng = Selection.SpecialCells(xlCellTypeConstants, 2)
With WorksheetFunction
For Each cell In rng
If Len(cell) > 2 Then
Select Case UCase$(.Trim(cell))
Case "ALABAMA"
cell = "AL"
Case "ALASKA"
cell = "AK"
Case "ARIZONA"
cell = "AZ"
Case "ARKANSAS"
cell = "AK"
Case "CALIFORNIA"
cell = "CA"
Case "COLORADO"
cell = "CO"
Case "CONNECTICUT"
cell = "CT"
Case "DISTRICT OF COLUMBIA"
cell = "DC"
Case "DELAWARE"
cell = "DE"
Case "FLORIDA"
cell = "FL"
Case "GEORGIA"
cell = "GA"
Case "HAWAII"
cell = "HI"
Case "IDAHO"
cell = "ID"
Case "ILLINOIS"
cell = "IL"
Case "INDIANA"
cell = "IN"
Case "IOWA"
cell = "IA"
Case "KANSAS"
cell = "KS"
Case "KENTUCKY"
cell = "KY"
Case "LOUISIANA"
cell = "LA"
Case "MAINE"
cell = "ME"
Case "MARYLAND"
cell = "MD"
Case "MASSACHUSETTS"
cell = "MA"
Case "MICHIGAN"
cell = "MI"
Case "MINNESOTA"
cell = "MN"
Case "MISSISSIPPI"
cell = "MS"
Case "MISSOURI"
cell = "MO"
Case "MONTANA"
cell = "MT"
Case "NEBRASKA"
cell = "NE"
Case "NEVADA"
cell = "NV"
Case "NEW HAMPSHIRE"
cell = "NH"
Case "NEW JERSEY"
cell = "NJ"
Case "NEW MEXICO"
cell = "NM"
Case "NEW YORK"
cell = "NY"
Case "NORTH CAROLINA"
cell = "NC"
Case "NORTH DAKOTA"
cell = "ND"
Case "OHIO"
cell = "OH"
Case "OKLAHOMA"
cell = "OK"
Case "OREGON"
cell = "OR"
Case "PENNSYLVANIA"
cell = "PA"
Case "PUERTO RICO"
cell = "PR"
Case "RHODE ISLAND"
cell = "RI"
Case "SOUTH CAROLINA"
cell = "SC"
Case "SOUTH DAKOTA"
cell = "SD"
Case "TENNESSEE"
cell = "TN"
Case "TEXAS"
cell = "TX"
Case "UTAH"
cell = "UT"
Case "VERMONT"
cell = "VT"
Case "VIRGINIA"
cell = "VA"
Case "WASHINGTON"
cell = "WA"
Case "WEST VIRGINIA"
cell = "WV"
Case "WISCONSIN"
cell = "WI"
Case "WYOMING"
cell = "WY"
' special cases
Case "ONTARIO"
cell = "ON"
Case "QUEBEC"
cell = "QC"
Case "BRITISH COLUMBIA"
cell = "BC"
Case "VIRGIN ISLANDS"
cell = "VI"
Case "ENGLAND"
cell = "UK"
Case "NOVA SCOTIA"
cell = "NS"
Case Else
End Select
End If
Next cell
End With
ActiveCell.Select
End Sub
I also included some other common abbreviations like UK, VI, etc. Feel free to add/subtract as needed.
