-
Using VLOOKUP to convert US State Abbreviations ...
-
Hi Greg,
Welcome to ExcelFox !!
Convert the list into a table. In Excel select the range > click on Insert menu > Table > OK.
Now you can use this table across the workbook.
The formula would be:
=VLOOKUP(A2,Table1,2,0)
-
Hi,
Thanks, this reply is very helpful however I need to re-state my request. I wish to create a custom function (i.e. Abbrev2StName) which can be accessed from any Excel worksheet. The Abbrev2StName function would call a cell location containing the state abbreviation and the result is the State name in a different cell location. For example:
The content of cell A2 contains CA
The content of cell B2 contains =Abbrev2StName(A2)
The result in cell B2 contains = California
Thanks in advance for your reply.
Kind regards,
Greg
-
Hi
Try this UDF
Code:
Function USSTATENAME(ByVal State) As String
Dim Abbrev
Dim States, x
Abbrev = Array("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", _
"Florida", "Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa", "Kansas", "Kentucky", _
"Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota", "Mississippi", _
"Missouri", "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")
States = Array("AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", _
"KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "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")
If TypeOf State Is Range Then State = State.Value
x = Application.Match(State, States, 0)
If Not IsError(x) Then
USSTATENAME = Abbrev(x - 1)
End If
End Function
and use
=USSTATENAME(A2)
-
Give this UDF a try which covers the 50 States plus US Territories and Armed Forces abbreviations as outlined in this wikipedia article...
List of U.S. state abbreviations - Wikipedia, the free encyclopedia
Code:
Function FullStateName(Abbreviation As String) As String
Dim Abbr As String, States() As String
If Len(Abbreviation) <> 2 Then Exit Function
Abbr = "AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO: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:AS:GU:MP:PR:VI:FM:MH:PW:AA:AE:AP:CZ:PI:TT:CM"
States = Split("Alabama:Alaska:Arizona:Arkansas:California:Colorado:Connecticut:Delaware:District of Columbia:Florida:Georgia:Hawaii:Idaho:Illinois:Indiana:Iowa:Kansas:Kentucky:Louisiana:Maine:Maryland:Massachusetts:Michigan:Minnesota:Mississippi:Missouri: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:American Samoa:Guam:Northern Mariana Islands:Puerto Rico:Virgin Islands, U.S. Minor Outlying Islands:Federated States of Micronesia:Marshall Islands:Palau:Armed Forces - Americas (except Canada):Armed Forces - Europe, Canada, Middle East, Africa:Armed Forces - Pacific:Canal Zone:Philippine Islands:Trust Territory of the Pacific Islands:Commonwealth of the Northern Mariana Islands", ":")
FullStateName = States((InStr(Abbr, UCase(Abbreviation)) - 1) / 3)
End Function
I know this code looks like a mess, but if you copy/paste it into a standard basic code module, it will neaten up into 5 lines of code.
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FullStateName just like it was a built-in Excel function. For example,
=FullStateName(A2)
or
=FullStateName("NJ")
-
Awesome! Both "USSTATENAME" and "FullStateName" functions work. I will be using "FullStateName" because it includes more abbreviations. Thanks, I appreciate your prompt replies to this request! :cheers:
-Greg
