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

 

[rishi.ssh]

First of all i apologize as i just tried ur code using as a function....so i returned false...i did not tried running code after selecting the code till yesterday....but i would like to tell you that...i have sucessfully saved your macro..and tried running it...to my surprise it brought me result as i desired...it was like miracle in frnt of me...seeing my fetched result...That space issue which u wrote later i dont think it was required...it was so smoothly fetching all the result..Thankx a lot buddy..u helped me a lot...till today i used to devote almost one and a half hour on this looking and finding manually the numbers...but today because of ur great help i completed the same work in 15 min..by running the macro and than vlookup. Just i faced problem that after approximately 2000 thae macro stoped giving me some error of objust range...i donk know what was that..so i shortened my file from 7000 entried to 2500:....which i needed....i dont know why that happned but nevermind what i wanted exactly i got..the best past was when a reference code was in 3 alpha and numberical the micro added a "-" between then...doing such 99% problem was solved...running macro is more simplier that using formula....Thankx everyone for your kind support...God bless u all I have other questions also but i will ask that in other thread This was the biggest issue for me..and luckly it got solved in less than a week....Have a nice day

 

[rishi.ssh]

It will be of great help if someone could theoretically explain me as what each line meant and did in this code..it is just for knowlegle as if smday smone ask me about the working of this code i could explain them. Thankx.

 

[xladept]

Hi Rishi.ssh,

I'm glad that it has worked out for you.

I've annotated the AlphaNum routine:

Sub AlphaNum()
Dim S As String, j As Integer, k As Integer
S = ActiveCell
If S = "" Then Exit Sub [COLOR=SeaGreen]'Quit when a null cell is encountered[/COLOR]

For j = 1 To Len(S) - 3
If IsAlpha(Mid(S, j, 1)) Then
    If IsAlpha(Mid(S, j + 1, 1)) Then
        If IsAlpha(Mid(S, j + 2, 1)) Then
            GoTo Procede  [COLOR=SeaGreen]'Opening routine has found 3 alphabetic characters[/COLOR]
        End If
    End If
End If

GetNext: Next j
[COLOR=SeaGreen]'The initial condition of three letters is not met so enter a string zero[/COLOR]
ActiveCell.Offset(0, 1) = "0": GoTo NextRecord

Procede:
[COLOR=SeaGreen]'If there's a fourth alphabetic character go back to opening routine[/COLOR]
If IsAlpha(Mid(S, j + 3, 1)) Then GoTo GetNext
[COLOR=SeaGreen]'If the triplet is followed by a space - remove it (you could remove a zero too!)[/COLOR]
If IsSpace(Mid(S, j + 3, 1)) Then
S = Mid(S, 1, j + 2) & Mid(S, j + 4, Len(S) - 1)
End If
[COLOR=SeaGreen]'If the triplet is immediately followed by a number insert a dash[/COLOR]
If IsNumeric(Mid(S, j + 3, 1)) Then
S = Mid(S, 1, j + 2) & "-" & Mid(S, j + 3, Len(S))
End If
[COLOR=SeaGreen]'If the dash is immediately followed by a space - remove it[/COLOR]
If IsSpace(Mid(S, j + 4 + k, 1)) Then
S = Mid(S, 1, j + 3) & Mid(S, j + 5, Len(S)): End If

For k = 1 To 5 [COLOR=SeaGreen]'Check for digits (there may be 2,3 or 4)[/COLOR]

If Not IsNumeric(Mid(S, j + 3 + k, 1)) Then Exit For
Next k  [COLOR=SeaGreen]' Stop cycling when no digit is found[/COLOR]
[COLOR=SeaGreen]'If there aren't 2,3 or 4 digits - go back to the opening routine[/COLOR]
If (k < 2 Or k > 5) Then GoTo GetNext
[COLOR=SeaGreen]'All conditions are met - so post result[/COLOR]
ActiveCell.Offset(0, 1) = Mid(S, j, 3 + k)
[COLOR=SeaGreen]'Only 3+k because the loop ends on the test failing[/COLOR]
NextRecord:
[COLOR=SeaGreen]'Advance a row[/COLOR]
ActiveCell.Offset(1, 0).Select
[COLOR=SeaGreen]'Run the macro for the next entry[/COLOR]
AlphaNum

End Sub

<table border="0" cellpadding="0" cellspacing="0" width="342"><colgroup><col width="342"></colgroup><tbody><tr height="17"> <td style="height:12.75pt;width:257pt" height="17" width="342">You can't do one thing. XLAdept</td> </tr></tbody></table>

 

[Rick Rothstein]

Here is a shorter macro (there are only 23 total code lines in it) for you to consider... it is completely self-contained and does not make use of any separate functions. I have also extensively commented the code so you can follow the logic behind it...

Sub AlphaNum()
  Dim X As Long, CellVal As String, Cell As Range
  '  Look at each cell in the selection
  For Each Cell In Selection
    '  We are going to reference the value in the cell several times,
    '  it is more efficient to repeatedly reference a String value than
    '  it is to repeatedly reference a cell directly, so we put the cell
    '  value into a String variable and use that
    CellVal = Cell.Value
    '  We now iterate the characters in the String
    For X = 1 To Len(CellVal)
      '  Now we look at each group of 3 characters in a row starting from the
      '  beginning of the String until we find 3 upper case letters in a row
      If Mid(CellVal, X) Like "[A-Z][A-Z][A-Z]*" Then
        '  Check if the 2 characters after the 3 upper case letters are digits
        If Mid(CellVal, X + 3, 2) Like "##" Then
          '  If so, put a dash in front of them
          CellVal = WorksheetFunction.Replace(CellVal, X + 3, 0, "-")
        '  Check if the 3 characters after the 3 upper case letters are {dash}{space}{digit}
        ElseIf Mid(CellVal, X + 3, 3) Like "- #" Then
          '  If so, remove the space and leave the {dash}{digit}
          CellVal = WorksheetFunction.Replace(CellVal, X + 3, 2, "-")
        End If
        '  The previous If..Then block guarantees that we are looking
        '  at 3 upper case letters followed by a dash. So we check how
        '  many characters after the dash are, in fact, digits.
        '  --------------------------------------------------------------
        '  This section checks if there are 4 digits following  the dash...
        '  if so, assign the 3 upper case letters, the dash, and the 4 digits
        '  to the cell in the next column and then exit the inner loop.
        If Mid(CellVal, X) & " " Like "[A-Z][A-Z][A-Z]-####[!0-9]*" Then
          Cell.Offset(, 1).Value = Mid(CellVal, X, 8)
          Exit For
        '  This section checks if there are 3 digits following  the dash...
        '  if so, assign the 3 upper case letters, the dash, and the 3 digits
        '  to the cell in the next column and then exit the inner loop.
        ElseIf Mid(CellVal, X) & " " Like "[A-Z][A-Z][A-Z]-###[!0-9]*" Then
          Cell.Offset(, 1).Value = Mid(CellVal, X, 7)
          Exit For
        '  This section checks if there are 2 digits following  the dash...
        '  if so, assign the 3 upper case letters, the dash, and the 2 digits
        '  to the cell in the next column and then exit the inner loop.
        ElseIf Mid(CellVal, X) & " " Like "[A-Z][A-Z][A-Z]-##[!0-9]*" Then
          Cell.Offset(, 1).Value = Mid(CellVal, X, 6)
          Exit For
        End If
      End If
    Next
  Next
End Sub

 

[rishi.ssh]

Hey thankx rick for this new short version...both works flawless...and i am thankfull to both of you for providing me the information i needed....God bless you both..Have a nice day.

 

[rishi.ssh]

Hi friends m back...but this time the issue is very minor.....actually..everything is working fine...but is this macro capable to handling data from cells more than 2500:...because many times i tried using it on my file...but everytime it stopped at arount row 2500:...i mean at a2500:..... Smthing error like object rage failed....shows up...but its not a big issur for me..because i usually compact my data to some low count..than run the macro..and it works fine..but i was just curious enough to learn wht dat error pops up when rows are more than 2500...any soulution...??

 

[rishi.ssh]

Anyone..??

 

[Rick Rothstein]

Hi friends m back...but this time the issue is very minor.....actually..everything is working fine...but is this macro capable to handling data from cells more than 2500:...because many times i tried using it on my file...but everytime it stopped at arount row 2500:...i mean at a2500:..... Smthing error like object rage failed....shows up...but its not a big issur for me..because i usually compact my data to some low count..than run the macro..and it works fine..but i was just curious enough to learn wht dat error pops up when rows are more than 2500...any soulution...??

Do you mean you select more than 2500 rows, but it errors out when it reaches A2500? Also, what is the exact error message you get?

 

[xladept]

Hi Rishi.ssh,

Are you using my routine or Rick's - If you're using Rick's, it depends upon the selection. If you're using mine, I'll look into it further.

 

[rishi.ssh]

@xladept...i am using your macro......ya actually i usually have 6k data...but i mostly bring it to 2500 and it works...but when it the rows are slightly more than i said...it stops...with some error tomm i will poast exact error...i googled it also for what it meant but i could nt find any satisfying answer...but..thanks bro..also rick i cant forget him..for time u both gave to my problem..please ignore this...as i dont have really a problem..just i was curious a little...because my works gets completed in data less that 2500 in 99% case...kindly look my other thread for extracting data from outlook email...please...it will be kind enough...thankx.