Using VLOOKUP to convert US State Abbreviations ...

[gamouning]

Hi,

Can you give me some help with using a VLOOKUP function that converts US State abbreviations into their respective State names?

For example, I want to convert AL to Alabama, AK to Alaska, AZ to Arizona, etc.

Currently, I have the following VLOOKUP statement in cell B2:

=VLOOKUP(A2, Abbrevs, 2, FALSE)

A2 contains the abbreviated state name
Abbrevs is a worksheet which lists abbreviations and state names next to each other in different columns.

I want to make this function accessible from any Excel worksheet.

Thanks in advance for your help.

Greg


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

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]

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]

Hi

Try this UDF


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]

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 (http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations)


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:N C: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:Colorad o:Connecticut:Delaware:District of Columbia:Florida:Georgia:Hawaii:Idaho:Illinois:Ind iana:Iowa:Kansas:Kentucky:Louisiana:Maine:Maryland :Massachusetts:Michigan:Minnesota:Mississippi:Miss ouri: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:Washi ngton: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]

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