Return US State Name or Territory from USPS Abbrevation - an example

  • Thread starter DataFreakFromUtah
  • Start date
D

DataFreakFromUtah

No question here. Just a custom function for the archive.

Search terms: Return Retrieve Get USPS State Territory Military Armed
Forces Postal Abbreviation United States US Postal Service
Abbreviation U.S. State and Territory Name function U.S.P.S.


Function StateNameFromAbbrev(StateAbbreviation As String)
'Returns the U.S. State or Territory Name based on USPS
State/Territory
'abbreviation.
On Error Resume Next

Select Case UCase(StateAbbreviation)
Case "AL"
StateNameFromAbbrev = "Alabama"
Case "AK"
StateNameFromAbbrev = "Alaska"
Case "AS"
StateNameFromAbbrev = "American Samoa"
Case "AZ"
StateNameFromAbbrev = "Arizona "
Case "AR"
StateNameFromAbbrev = "Arkansas"
Case "AE"
StateNameFromAbbrev = "Armed Forces Africa "
Case "AA"
StateNameFromAbbrev = "Armed Forces Americas "
Case "AE"
StateNameFromAbbrev = "Armed Forces Canada"
Case "AE"
StateNameFromAbbrev = "Armed Forces Europe"
Case "AE"
StateNameFromAbbrev = "Armed Forces Middle East"
Case "AP"
StateNameFromAbbrev = "Armed Forces Pacific"
Case "CA"
StateNameFromAbbrev = "California "
Case "CO"
StateNameFromAbbrev = "Colorado "
Case "CT"
StateNameFromAbbrev = "Connecticut"
Case "DE"
StateNameFromAbbrev = "Delaware"
Case "DC"
StateNameFromAbbrev = "District Of Columbia"
Case "FM"
StateNameFromAbbrev = "Federated States Of Micronesia"
Case "FL"
StateNameFromAbbrev = "Florida"
Case "GA"
StateNameFromAbbrev = "Georgia"
Case "GU"
StateNameFromAbbrev = "Guam "
Case "HI"
StateNameFromAbbrev = "Hawaii"
Case "ID"
StateNameFromAbbrev = "Idaho"
Case "IL"
StateNameFromAbbrev = "Illinois"
Case "IN"
StateNameFromAbbrev = "Indiana"
Case "IA"
StateNameFromAbbrev = "Iowa"
Case "KS"
StateNameFromAbbrev = "Kansas"
Case "KY"
StateNameFromAbbrev = "Kentucky"
Case "LA"
StateNameFromAbbrev = "Louisiana"
Case "ME"
StateNameFromAbbrev = "Maine"
Case "MH"
StateNameFromAbbrev = "Marshall Islands"
Case "MD"
StateNameFromAbbrev = "Maryland"
Case "MA"
StateNameFromAbbrev = "Massachusetts"
Case "MI"
StateNameFromAbbrev = "Michigan"
Case "MN"
StateNameFromAbbrev = "Minnesota"
Case "MS"
StateNameFromAbbrev = "Mississippi"
Case "MO"
StateNameFromAbbrev = "Missouri"
Case "MT"
StateNameFromAbbrev = "Montana"
Case "NE"
StateNameFromAbbrev = "Nebraska"
Case "NV"
StateNameFromAbbrev = "Nevada"
Case "NH"
StateNameFromAbbrev = "New Hampshire"
Case "NJ"
StateNameFromAbbrev = "New Jersey"
Case "NM"
StateNameFromAbbrev = "New Mexico"
Case "NY"
StateNameFromAbbrev = "New York"
Case "NC"
StateNameFromAbbrev = "North Carolina"
Case "ND"
StateNameFromAbbrev = "North Dakota"
Case "MP"
StateNameFromAbbrev = "Northern Mariana Islands"
Case "OH"
StateNameFromAbbrev = "Ohio"
Case "OK"
StateNameFromAbbrev = "Oklahoma"
Case "OR"
StateNameFromAbbrev = "Oregon"
Case "PW"
StateNameFromAbbrev = "Palau"
Case "PA"
StateNameFromAbbrev = "Pennsylvania"
Case "PR"
StateNameFromAbbrev = "Puerto Rico"
Case "RI"
StateNameFromAbbrev = "Rhode Island"
Case "SC"
StateNameFromAbbrev = "South Carolina"
Case "SD"
StateNameFromAbbrev = "South Dakota"
Case "TN"
StateNameFromAbbrev = "Tennessee"
Case "TX"
StateNameFromAbbrev = "Texas"
Case "UT"
StateNameFromAbbrev = "Utah"
Case "VT"
StateNameFromAbbrev = "Vermont"
Case "VI"
StateNameFromAbbrev = "Virgin Islands"
Case "VA"
StateNameFromAbbrev = "Virginia "
Case "WA"
StateNameFromAbbrev = "Washington"
Case "WV"
StateNameFromAbbrev = "West Virginia"
Case "WI"
StateNameFromAbbrev = "Wisconsin"
Case "WY"
StateNameFromAbbrev = "Wyoming"

'If the TargetCell isn't one of the above listed, function
'returns nothing


Case Else
StateNameFromAbbrev = ""

End Select
End Function
 
S

Steve Garman

Not wishing to be ungrateful but you have a number of cases saying:
Case "AE"
which should probably be something else.

Unfortunately, I can't correct them as I don't know the right answers.
 
Top