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

[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

 

[pikus]

Cool! That was very thoughtful. - Piku

 

[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.

 

[Steve Garman]

Having looked further, I see I misunderstood the problem
http://www.usps.com/ncsc/lookups/usps_abbreviations.html
gives the same abbreviations you do.

However, the second and subsequent versions will never be reached.
Perhaps something like:

Case "AE"
StateNameFromAbbrev = "Armed Forces (indeterminate) "