U.S State abbreviation to full state name

rishi.ssh

Board Regular
Joined
May 4, 2012
Messages
60
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Create a table of codes and full names in say columns L and M, and then use VLOOKUP to get the state name

=VLOOKUP(A2,L:M,2,False)
 
Upvote 0
Thankx for cuming up with that....but i was wondering for an excel macro so that each time while working i probably just could run it and work easily. And can you help me up with my second issue i need it badly.
 
Upvote 0
Formulae are usually easier and better than code. Use formulae, and you won't need to run it again.
 
Upvote 0
Yes i agrre with you that formulas are easier to work with...but wht my job requires is smthing different...on daily basis i have 2 diff sheets...one is authentic software generated...and other is manual entered...i need to veryfy both data...so in that case when it comes to matching states names i usually do a vlookup in one sheet and bring state names than i cannot match true and false..because it will never be true...so i need to manually filter and check both sides..there are Thousands of data...so its veyr time consuming to do vlookup so many times...i hope wht i mean..but for sure u r correct atleast when i do a vlookup in one sheet for state...it can be simpler and will hardly take 5 min..thanks alot for ur help...but still i want to learn macro...and could u please help me out with my send question...??? i badly need it more than that state issue.
 
Upvote 0
Fill in the rest of the states in the case statements and you will be good to go.

Feel free to change the range of r = range("A1:52") to suite your needs.

And formulas aren't better than VBA. VBA Rules!

Code:
Sub States()
Dim r As Range, cel As Range
Set r = Range("A1:A52")

For Each cel In r

    Select Case cel.Value
    
        Case "AL"
            cel.Offset(, 1).Value = "Alabama"
        
        Case "CA"
            cel.Offset(, 1) = "California"
            
    End Select
    
Next cel
End Sub
 
Upvote 0
As or your second question, assuming again that your information is in column A, this code will look at the values in that range, and if they contain a hyphen, then it will collect those values and put them in Column B.

Code:
Sub test()
Dim iCol()
Dim r As Range, cel As Range
Set r = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Dim x As Long
For Each cel In r
    If InStr(1, cel, "-") > 1 Then
        ReDim Preserve iCol(0 To x)
        iCol(x) = cel.Value
        x = x + 1
    End If
Next cel
Set r = r.Offset(, 1).Resize(x, 1)
For x = 0 To UBound(iCol)
    r.Cells(x + 1, 1).Value = iCol(x)
Next x
End Sub
 
Upvote 0
And actually, this i slightly less complicated but should yield the same result.

Code:
Sub test2()
Dim r As Range, cel As Range
Set r = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Dim x As Long
x = 1
For Each cel In r
    If InStr(1, cel, "-") > 1 Then
        Cells(x, cel.Column() + 1).Value = cel.Value
        x = x + 1
    End If
Next cel
End Sub
 
Upvote 0
And formulas aren't better than VBA. VBA Rules!

We will have to agree to disagree on that. You can also use functions in VBA,

Code:
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 Variant
Dim vecStateIds As Variant
Dim 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 cell
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top