Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Return US State Name or Territory from USPS Abbrevation - an example
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 36
Default Return US State Name or Territory from USPS Abbrevation - an example
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Return US State Name or Territory from USPS Abbrevation - an example
Cool! That was very thoughtful. - Piku

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Return US State Name or Territory from USPS Abbrevation - anexample
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.

DataFreakFromUtah wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Return US State Name or Territory from USPS Abbrevation - anexample
Having looked further, I see I misunderstood the problem
http://www.usps.com/ncsc/lookups/usp...eviations.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) "


Steve Garman wrote:

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.

DataFreakFromUtah wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
replace state names with state code abbreviations se7098 Excel Worksheet Functions 3 July 25th 09 06:41 PM
Return to a default state??? lost and confused in excel-land[_2_] Excel Worksheet Functions 1 July 13th 08 11:47 PM
Converting State Names to State Abbreviations aznate Excel Discussion (Misc queries) 1 October 20th 06 06:52 AM
Dynamic territory list Bill_S Excel Worksheet Functions 2 October 11th 06 03:39 AM
VBA...How do you state a carriage return? woz999[_2_] Excel Programming 4 April 28th 04 08:51 PM


All times are GMT +1. The time now is 04:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement