Page 1 of 8 123 ... LastLast
Results 1 to 10 of 77

U.S State abbreviation to full state name

This is a discussion on U.S State abbreviation to full state name within the Excel Questions forums, part of the Question Forums category; Hi all, First of all i would like to introduce myself i am new this forum and also new to ...

  1. #1
    Board Regular
    Join Date
    May 2012
    Posts
    60

    Default U.S State abbreviation to full state name

    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

  2. #2
    Board Regular
    Join Date
    May 2012
    Posts
    60

    Default Re: U.S State abbreviation to full state name

    Please somebody...if nt d secnd one than just the first one atleat.

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    509

    Default Re: U.S State abbreviation to full state name

    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)

  4. #4
    Board Regular
    Join Date
    May 2012
    Posts
    60

    Default Re: U.S State abbreviation to full state name

    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.

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    509

    Default Re: U.S State abbreviation to full state name

    Formulae are usually easier and better than code. Use formulae, and you won't need to run it again.

  6. #6
    Board Regular
    Join Date
    May 2012
    Posts
    60

    Default Re: U.S State abbreviation to full state name

    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.

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: U.S State abbreviation to full state name

    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
    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

    If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut.

  8. #8
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: U.S State abbreviation to full state name

    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
    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

    If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut.

  9. #9
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: U.S State abbreviation to full state name

    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
    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

    If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut.

  10. #10
    Board Regular
    Join Date
    Jan 2012
    Posts
    509

    Default Re: U.S State abbreviation to full state name

    Quote Originally Posted by lrobbo314 View Post
    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

Page 1 of 8 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com