U.S State abbreviation to full state name

[rishi.ssh]

Hi all,
First of all i would like to introduce myself i am new this forum and also new to excel macro function and how it works.
I am facing some difficulty while using excel....i have alot of data everyday to work upon....
Dear friends i would like your help if someone could help me out to replace all the U.S states abbreviations in full name....For example AZ to ARIZONA,CO to COLORADO and so on for all short names to full.....My abbreviation is in Column B of a sheet....down by down and many are there...i just want that a macro just replace all the abbreviation and convert them to Full name....Please i am urgently in need of this...and alot of time will be saved of mine....Your help will be highly appreciated.



Secondly is there and way of custom filtering of data in a column so that i just have only the value which is attached with a hyphen i mean "-"
for example it will be like xyz-452,kju-111,RTL-524. More details i will provide that Suppose my data is in column A1 "rgtf | 526435 | puj-624 | 363rgy" column A2 "5171 | TYT-415 | puj-624 | 3686A" and so on similar with in all column A down by down..... I want also an macro or any way so that i have just data in column A that filters my result to just have Values attached with an hyphen(-)..... Like in a1 puj-624....and in a2 TYT-415....and all other is removed...can this happen...i cannot use delimate or width...saperation....Lower and upper case both...
Note my those data is in column A....also There is everytime 3 alphabets than an hyphen(-) and than just 3 numbers....
Please help.
Thankx in advance

 

[IonutC]

Thank you! I will give it a try tomorow! Have a great week ahead!

 

[IonutC]

Thank you! I will give it a try tomorow! Have a great week ahead!

Hi Rick,

Is not working

I am trying to see in column Z the full name of a state.==>Z2=Alabama, Z3=Alaska etc.

Ex.

I want to write a code that will do like a =vlookup(D2,{IsoAbreviationsList},2,0).


Col.A Col.B Col.C Col.D Col.E Col.Z

Name	Address	City	State	Country
X	X	Wesley Chapel	AL	USA
Y	Y	Alpharetta	AK	USA
Z	Z	Jacksonville	AZ	USA
ZZ	ZZ	Cumming	AR	USA
MN	MN	Miramar	CA	USA
X	X	Rockaway	CO	USA
Y	Y	St. Petersburg	CT	USA

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

FYI The ISoList is like this:

United States
AL	Alabama	United States of America
AK	Alaska	United States of America
AZ	Arizona	United States of America
AR	Arkansas	United States of America
CA	California	United States of America

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you for your prompt answer.

Have a great day,
Ionut

 

[IonutC]

Hello,

So I have this code that is working perfectly:

Sub GetStateNames()


Const StateNames As String = _
    "Alabama,Alaska,American Samoa,Arizona,Arkansas,California,Colorado,Connecticut,District of Columbia,Delaware,Florida," & _
    "Federated States of Micronesia, Georgia,Guam, Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _
    "Maryland,Massachusetts,Marshall Islands,Michigan,Northern Mariana Islands, Mississippi,Missouri,Minnesota,Montana,Nebraska," & _
    "Nevada,New Hampshire,New Jersey,New Mexico,New York,North Carolina,North Dakota," & _
    "Ohio,Oklahoma,Oregon,Pennsylvania,Palau,Puerto Rico, Rhode Island,South Carolina,South Dakota,Tennessee," & _
    "Texas,U.S. Minor Outlying Islands, Utah,Virgin Islands, Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"


Const StateIds As String = _
    "AL,AK,AS,AZ,AR,CA,CO,CT,DC,DE,FL,FM,GA,GU,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MH,MI,MP,MS,MO,MN,MT," & _
    "NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,PW,PR,RI,SC,SD,TN,TX,UM,UT,VI,VT,VA,WA,WV,WI,WY"


Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range


    vecStateIds = Split(StateIds, ",")
    vecStateNames = Split(StateNames, ",")
    
 On Error Resume Next
    
    For Each cell In Range("D2:D200000")
    
        If cell.Value <> "" Then
        
            cell.Offset(0, 15).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0))
        End If
    Next cell
End Sub

My issue is the fact that col.D which represents the states and has all the abbreviation, is changing all the time....
So I want to customize the above code to be sensitive to a Range. I can create a Range with the state into the working excel and I want my code to automatically detect the col. of the Range so it will automatically Offset the full name of the state into a given column (15) as per the code.

Hope that I was clear.

Please let me know if you can help me with my thing,
IonutC

 

[Rick Rothstein]

Hi Rick,

Is not working

I am trying to see in column Z the full name of a state.==>Z2=Alabama, Z3=Alaska etc.

Ex.

I want to write a code that will do like a =vlookup(D2,{IsoAbreviationsList},2,0).

Oh, I see what the problem is. My original code (which was written in response to the original question) looked up the full state name and supplied the abbreviation for it.... you want the reverse. Here is my code modified to look up the abbreviation and supply the full state name (and I adjusted it to output to Column Z, not Column S)...

Sub GetStateNames()
  
  Dim Cell As Range
  
  Const States As String = "AL,Alabama,AK,Alaska,AZ,Arizona,AR,Arkansas,CA," & _
                           "California,CO,Colorado,CT,Connecticut,DE," & _
                           "Delaware,FL,Florida,GA,Georgia,HI,Hawaii,ID," & _
                           "Idaho,IL,Illinois,IN,Indiana,IA,Iowa,KS," & _
                           "Kansas,KY,Kentucky,LA,Louisiana,ME,Maine,MD," & _
                           "Maryland,MA,Massachusetts,MI,Michigan,MS," & _
                           "Mississippi,MO,Missouri,MN,Minnesota,MT," & _
                           "Montana,NE,Nebraska,NV,Nevada,NH,New Hampshire,NJ," & _
                           "New Jersey,NM,New Mexico,NY,New York,NC," & _
                           "North Carolina,ND,North Dakota,OH,Ohio,OK," & _
                           "Oklahoma,OR,Oregon,PA,Pennsylvania,RI," & _
                           "Rhode Island,SC,South Carolina,SD,South Dakota,TN," & _
                           "Tennessee,TX,Texas,UT,Utah,VT,Vermont,VA,Virginia,WA," & _
                           "Washington,WV,West Virginia,WI,Wisconsin,WY,Wyoming"
  
  For Each Cell In Range("D2", Cells(Rows.Count, "D").End(xlUp))
    If InStr(States, Cell.Value) And Len(Cell.Value) = 2 Then
      Cell.Offset(0, 15).Value = Split(Split(States, Cell.Value & ",")(1), ",")(0)
    End If
  Next

End Sub

My issue is the fact that col.D which represents the states and has all the abbreviation, is changing all the time....
So I want to customize the above code to be sensitive to a Range. I can create a Range with the state into the working excel and I want my code to automatically detect the col. of the Range so it will automatically Offset the full name of the state

If I understand correctly, you want the code to work automatically instead of having to run it each time... is that correct? I am about to go to sleep, so I check your answer when I get up.

 

[IonutC]

Yes Sir indeed!
Thank you for your answer.

I

 

[Rick Rothstein]

If I understand correctly, you want the code to work automatically instead of having to run it each time... is that correct?

Yes Sir indeed!

Okay, we will need event code to do that (see below how to install it as it goes in a different type of module than where macros go). But first, if your worksheet has existing data on it, then you must first run the macro I posted in Message #84 (change the 15 to 22 first, though, so it outputs to the correct column) in order to populate Column Z with the state names for your existing state abbreviations. Once you have done that, you can delete the macro as it will no longer be needed... the event code below will handle populating Column Z from now on.

Private Sub Worksheet_Change(ByVal Target As Range)
  Static States As String
  If Len(States) = 0 Then
    States = "AL,Alabama,AK,Alaska,AZ,Arizona,AR,Arkansas,CA," & _
             "California,CO,Colorado,CT,Connecticut,DE," & _
             "Delaware,FL,Florida,GA,Georgia,HI,Hawaii,ID," & _
             "Idaho,IL,Illinois,IN,Indiana,IA,Iowa,KS," & _
             "Kansas,KY,Kentucky,LA,Louisiana,ME,Maine,MD," & _
             "Maryland,MA,Massachusetts,MI,Michigan,MS," & _
             "Mississippi,MO,Missouri,MN,Minnesota,MT," & _
             "Montana,NE,Nebraska,NV,Nevada,NH,New Hampshire,NJ," & _
             "New Jersey,NM,New Mexico,NY,New York,NC," & _
             "North Carolina,ND,North Dakota,OH,Ohio,OK," & _
             "Oklahoma,OR,Oregon,PA,Pennsylvania,RI," & _
             "Rhode Island,SC,South Carolina,SD,South Dakota,TN," & _
             "Tennessee,TX,Texas,UT,Utah,VT,Vermont,VA,Virginia,WA," & _
             "Washington,WV,West Virginia,WI,Wisconsin,WY,Wyoming"
  End If
  If Target.Row > 1 And Target.Column = 4 Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    If InStr(States, Target.Value) And Len(Target.Value) = 2 Then
      Target.Offset(, 22) = Split(Split(States, Target.Value & ",")(1), ",")(0)
    Else
      Target.Offset(, 22) = ""
    End If
    Application.EnableEvents = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[IonutC]

Thank you Rick!
You are the best!

 

[Rick Rothstein]

Thank you Rick!
You are the best!

You are quite welcome. I forgot to mention in my last message... the event code I posted does not require you to enter the state abbreviations in upper case letters... you can enter it in lower case letters if that is more convenient and the code will automatically make the letters upper case for you.

 

[Peter_SSs]

@IonutC
I don't know exactly how you will be using the sheet, but a couple of points re Rick's code:

1. It will error if multiple cells in column D are altered at once (eg you might decide to delete 2 or 3 of the state codes at once or copy/paste a number of state codes from another source, or enter the same state code in a group of cells with Ctrl+Enter)

2. It will not error, but also will not enter the state name, if any row(s) of data (eg A5:E5 or C6:D9) are pasted into the sheet. Similarly it will not remove the full state name if any row(s) of cells (again, say, A5:E5) are deleted at once.

Using Rick's code as a basis, this code attempts to address those extra issues. The fact that my States list is delimited by "|" instead of "," is not relevant. It is just how my code ended up after trying a few different things.

Private Sub Worksheet_Change(ByVal Target As Range)
  Static States As String
  Dim c As Range, Changed As Range
  
  Set Changed = Intersect(Target, Columns("D"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    If Len(States) = 0 Then
      States = "|AL|Alabama|AK|Alaska|AZ|Arizona|AR|Arkansas|CA|California|CO|Colorado|" & _
              "CT|Connecticut|DE|Delaware|FL|Florida|GA|Georgia|HI|Hawaii|" & _
              "ID|Idaho|IL|Illinois|IN|Indiana|IA|Iowa|KS|Kansas|" & _
              "KY|Kentucky|LA|Louisiana|ME|Maine|MD|Maryland|MA|Massachusetts|" & _
              "MI|Michigan|MS|Mississippi|MO|Missouri|MN|Minnesota|MT|Montana|" & _
              "NE|Nebraska|NV|Nevada|NH|New Hampshire|NJ|New Jersey|NM|New Mexico|" & _
              "NY|New York|NC|North Carolina|ND|North Dakota|OH|Ohio|OK|Oklahoma|" & _
              "OR|Oregon|PA|Pennsylvania|RI|Rhode Island|SC|South Carolina|SD|South Dakota|" & _
              "TN|Tennessee|TX|Texas|UT|Utah|VT|Vermont|VA|Virginia|" & _
              "WA|Washington|WV|West Virginia|WI|Wisconsin|WY|Wyoming|"
    End If
    Application.EnableEvents = False
    For Each c In Changed
      c.Value = UCase(c.Value)
      c.Offset(, 22).Value = Split(Split(States & c.Value & "||", "|" & c.Value & "|")(1), "|")(0)
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[IonutC]

Thank you Peter for your reply!

Now I am trying to figure out how can I insert into the code a Dynamic Range. Now the code has the col.D in it. But I have a lot of excels when the state is not only in col.D.

So I want after I receive the excel to set the Range withing the Ribon==>Formulas==>CreateFromSelection==>TopRow and this way based on Header we can see the range of the State.

And I need the code to autodetect the new Range Created (E.g. F) and based on this new range created and based on the Dynamic range created in it to automaticaly populate the entire State Name into col.Z.


Have a great week guys and I really appreciate your answer and learned a lot of things from your codes and comments!


Ionut