Results 1 to 6 of 6

Thread: Using VLOOKUP to convert US State Abbreviations ...

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    Using VLOOKUP to convert US State Abbreviations ...

    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/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    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=UgzMCQUIQgrbec400jl4AaABAg
    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=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    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=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:53 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    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

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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")
    Last edited by Rick Rothstein; 10-02-2012 at 09:03 PM.

  6. #6
    Junior Member
    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    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

Similar Threads

  1. VBA Macro To Convert Text To Proper Case
    By Howardc in forum Excel Help
    Replies: 4
    Last Post: 05-31-2013, 12:38 AM
  2. Replies: 0
    Last Post: 08-23-2012, 10:32 PM
  3. Replies: 0
    Last Post: 08-23-2012, 08:00 AM
  4. Help with a Vlookup and SUMIF
    By Lucero in forum Excel Help
    Replies: 4
    Last Post: 07-24-2012, 05:03 AM
  5. Convert Series into Range.
    By ayazgreat in forum Excel Help
    Replies: 13
    Last Post: 03-26-2012, 08:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •