Using VLOOKUP to convert US State Abbreviations ...

**gamouning** · 10-02-2012, 03:07 AM

**Admin** · 10-02-2012, 09:26 AM

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)

**gamouning** · 10-02-2012, 07:33 PM

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

**Admin** · 10-02-2012, 08:44 PM

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)

**Rick Rothstein** · 10-02-2012, 09:00 PM

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")

**gamouning** · 10-02-2012, 09:37 PM

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!

-Greg