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]

Hey friends i have tried providing you the data so that it can be easy for you to understand me...the previous solution was sucessful returning what i needed ...but it just filled on column b...one after another...i want to Let u know that there are several data in each column like In row 2...the data represent others in that same row... I things i do now is manually i search for that value and then look manually if other is correct or not against it....but if i just get what i need what almost worked in the solution provided above but could nt exactly helped me as what i wanted...friends if i can sort such data than i can simply vlookup from it and can easily do my work... But in previous solution that test(5) one the problem was that...when some cell had the value as cla-45 | 55Gjy it did not returned that cla part.. I want that if a value is taken from cell Suppose A5 THAN IT MUST RETURN ME IN CELL B5 NOT IN B4.....the haphazard is happening because all the cells in column b dosent have ABC-123 ....SOME have just numbers...so it must ignore such and return 0 OR blank IN JUST ITS NEXT right cell...i mean column b...the same row.......I hope experts now i have made it cleraly...I am very very thankfull to all of ur kind help and support in my complicated case.....i await you positive and helpful solution to this problem. ....regards,Rishi.ssh

 

[xladept]

Is it always 3 letters and a number with or without the dash that you want in column B?

<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>

 

[rishi.ssh]

Yes always 3 letters with dash and numbers(the numbers can be minimum 2 and maximum 4)....also some does not have number but Three alphabets is always there.... That previous one worked with it did not returned the result in same row of column B...... Some cells in column A may also not have what i need...so for that i should return 0 or blank atleast...but resulted data should always be in Same row... That above formula used delimate of | but if what is need is in starting that it did not returned that value...like if in cell a its is TAC-555 | 6494646 | 636TUY .....THAN tac-555 (as not returned...??? Also column A values are unique... I mean if In any cell there is 3 letter - and number...that in that cell you will not find similar value..I truely appreciate ur spirit of help...Thankx alot...

 

[xladept]

This works with the sample provided - I wrote the two functions rather than spend time looking up anything already canned. I hope that this will accomplish your task.

Function IsAlpha(S As String) As Boolean
Dim Alpha, n As Integer
Alpha = Array(" ", "A", "B", "C", "D", "E", "F", "G", _
"H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
"R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For n = 1 To 26
If S = Alpha(n) Then
IsAlpha = True: Exit Function: End If
Next n
End Function
Function IsSpace(S As String) As Boolean
If S = " " Then IsSpace = True
End Function
Sub AlphaNum()
Dim S As String, j As Integer, k As Integer
S = ActiveCell
If S = "" Then Exit Sub
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
        End If
    End If
End If

GetNext: Next j
ActiveCell.Offset(0, 1) = "0"

ActiveCell.Offset(1, 0).Select: Exit Sub
Procede:
If IsAlpha(Mid(S, j + 3, 1)) Then GoTo GetNext
If IsNumeric(Mid(S, j + 3, 1)) Then
S = Mid(S, 1, j + 2) & "-" & Mid(S, j + 3, Len(S))
End If

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

If Not IsNumeric(Mid(S, j + 3 + k, 1)) Then Exit For
Next k

If (k < 2 Or k > 4) Then GoTo GetNext

ActiveCell.Offset(0, 1) = Mid(S, j, 3 + k)

ActiveCell.Offset(1, 0).Select

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>

 

[rishi.ssh]

Sir i am sorry do not know how to use a function developed in macro i mean UDF. do i need to use it in the same way as a macro like i did copying pasting than ran the macro..i returned value..?? Something like this...You have worked a lot on my issue and i assure you i will definately try to use it...but first i should know exactly how to use this function macro..Thankx for helping....if possible tomm i will provide a sample file ..and will show exactly what i need....and how. But still first of all i will try what u posted today....your previous macro did worked..if only u could twist it some to return result in same row... I mean for A1 result should be in B1...for A2....B2....for A3 result should be in B3...than if suppose in A4 it could not find anything than B4 should return either blank or 0...or any error..... Thankxxxx a lot.

 

[Rick Rothstein]

Sir i am sorry do not know how to use a function developed in macro i mean UDF.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IsAlpha just like it was a built-in Excel function. For example,

=IsAlpha(A1)

 

[rishi.ssh]

Hmm..thankx rick just one question will it be saved for ever in my excel so that i can use it whenever i need...? Do i need to save it in personal.xlxs.

 

[xladept]

Hi Rishi.ssh,

Like Rick says just copy paste the whole thing into a module - you need a macro empowered spreadsheet to do it - I believe that the extension would be .xlsm

<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>

 

[rishi.ssh]

Thankx xladept...surely i will try tomm..and will let u know..about the result.Hopefully positive this time.

 

[xladept]

Hi Rishi.ssh,

I've made a few changes in the routine - After I posted, I realized that I hadn't checked for a space after the alphabetic triplet and I saw that I wasn't catching all the records with 4 numbers - use this new version, it works much better!

Function IsAlpha(S As String) As Boolean
Dim Alpha, n As Integer
Alpha = Array(" ", "A", "B", "C", "D", "E", "F", "G", _
"H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
"R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For n = 1 To 26
If S = Alpha(n) Then
IsAlpha = True: Exit Function: End If
Next n
End Function
Function IsSpace(S As String) As Boolean
If S = " " Then IsSpace = True
End Function
Sub AlphaNum()
Dim S As String, j As Integer, k As Integer
S = ActiveCell
If S = "" Then Exit Sub
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
        End If
    End If
End If

GetNext: Next j

ActiveCell.Offset(0, 1) = "0": GoTo NextRecord

Procede:

If IsAlpha(Mid(S, j + 3, 1)) Then GoTo GetNext

If IsSpace(Mid(S, j + 3, 1)) Then
S = Mid(S, 1, j + 2) & Mid(S, j + 3, Len(S))
End If

If IsNumeric(Mid(S, j + 3, 1)) Then
S = Mid(S, 1, j + 2) & "-" & Mid(S, j + 3, Len(S))
End If

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

If Not IsNumeric(Mid(S, j + 3 + k, 1)) Then Exit For
Next k

If (k < 2 Or k > 5) Then GoTo GetNext

ActiveCell.Offset(0, 1) = Mid(S, j, 3 + k)

NextRecord:

ActiveCell.Offset(1, 0).Select

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>