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

 

[xladept]

Hi Rishi.ssh,

Try this instead:

Sub ExtractIDComm(): Dim i As Integer, j As Integer, S As String
Dim k As Integer
S = ActiveCell
If S = "" Then Exit Sub
i = InStr(1, S, "ID:")
ActiveCell.Offset(0, 1) = Mid(S, i + 4, 19)
j = InStr(1, S, "COMISSION:"): j = j + 12: k = InStr(j, S, " ")
If k Then
ActiveCell.Offset(0, 2) = Mid(S, j - 1, k - j + 1)
Else
ActiveCell.Offset(0, 2) = Mid(S, j - 1, Len(S) - j + 2)
End If
ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub

 

[rishi.ssh]

Hey thankx xladept this is what i wanted now its working smoothly. Many many thankx to you man. Can i customize this macro to return both variable data..for B column also..as of nw i dont need such..because ID WILL BE be of fixed length..but just for knowledg how cn i also return variable lenght id...?? I mean can i use same way as i used for (0,2)..??

 

[rishi.ssh]

Solved

 

[xladept]

Great!

 

[miketran]

Hello,

I found your solution above and it is very close to what I am looking for, but not quite. I have a list of contacts that has a mix of states names and states initials...

I need a VBA script that will go down the list and replace the NAME of the state with the state abbreviation. Some of the state cells have bad data in them, so those cells need to be skipped past...

Sub GetStateNames()Const StateNames As String = _ "Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _ "Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _ "Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,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"Const StateIds As String = _ "AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,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"Dim vecStateNames As VariantDim vecStateIds As VariantDim cell As Range vecStateIds = Split(StateIds, ",") vecStateNames = Split(StateNames, ",") For Each cell In Range("A2:A200") If cell.Value <> "" Then cell.Offset(0, 1).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0)) End If Next cellEnd Sub</pre>

From this:
AL
Alaska
CA
New Jersey

To This:

AL
AK
CA
NJ

Any Ideas?

 

[Rick Rothstein]

I need a VBA script that will go down the list and replace the NAME of the state with the state abbreviation. Some of the state cells have bad data in them, so those cells need to be skipped past...

From this:
AL
Alaska
CA
New Jersey

To This:

AL
AK
CA
NJ

I would set up only one constant where the list consists of a state name followed immediately by its abbreviation. That way, if the cell contains a state name, you can use the Split function on it to get what follows and then use Split on it to retrieve the abbreviation. This is what I have in mind...

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

End Sub

 

[xladept]

Hi miketran,

It looks like Rick has solved your problem - thanks Rick!

 

[IonutC]

Hi there,

Thanks for sharing this code.

I have a quick one. If I have created already a range of the State called "State" and if I inserted this range into this code line [ For Each cell In Range("A2:A200"] so the code will become
For Each cell In Range("State") the above code will not work. What I have to do in order to run this code so I have all the state list based on a specified Range called "State".

Thank you,
IonutC

 

[IonutC]

And the code became like this..and is not working...What I did wrong?


Sub GetStateNames()


Const StateNames As String = _
"Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _
"Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _
"Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,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"


Const StateIds As String = _
"AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,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"


Dim vecStateNames As Integer


Dim vecStateIds As Variant
Dim cell As Range






vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")

For Each cell In Range("State").cell

If Cells.Value <> "" Then


cell.Offset(0, 15).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0))
End If
Next cell
End Sub

 

[Rick Rothstein]

And the code became like this..and is not working...What I did wrong?

If you are willing to use the code I posted in Message #76 as a base instead, then here is what it would look like with your required changes (shown in red) implemented...

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

End Sub