Using VLOOKUP to convert US State Abbreviations ...

[Rick Rothstein]

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

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