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]

Hi xladept
i tried that funcion and as u wrote now i also found some gap in that..as not all the desired result was fetching....i just came from work..
Just i faced problem is that after using that udf i was unable to save it permanently that function so when i closed that workbook and when i again opened..that function wasnt there...i want that ..that function gets installed in my system so that i can use it in any excel file i want....?? How can i do this...and i will try this new code of yours tomm.
thankx buddy.

 

[xladept]

Hi Rishi.ssh,

Replace the code for the space check with:

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

I forgot to change the indexing in the original.

Did you use your Personal Spreadsheet for the macros or did you use the Working sheet? You need to save it as macro empowered i.e. .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>

 

[xladept]

Hi Rishi.ssh,

Here's a book for you with the routines and the test data:

Well - I just found out that I can't attach anything - sorry!

 

[rishi.ssh]

Hi xladept..

I used my working sheet.... Please tell me clearly as what will i need to do in order to make sure the formula apper in all excel file i open?...and no problem i will replace the code with the last one you provided me...and u can upload d book smwhere and give me d link i will download ..
The macro got saved somehow and now it appears in all the excel i open..under view>macro....like this i also need that the formula which u made can also be called everytime when needed.thankx

 

[xladept]

Hi Rishi.ssh,

I guess you're all set then. The AlphaNum Macro is still pretty rough - you can learn a lot by cleaning it up. For instance, many of the Mid functions I just let end with Len(S) - it works but it's sloppy and, under scrutiny, you can probably find more haphazard code - I just wrote it yesterday morning after breakfast and didn't test it as I would have had I been on contract.

When 2007 came out I had it, but I really didn't like the Ribbon so I bought 2003 and replaced my 2007 with that (I had been using 2000 until then).

Good Luck,

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

Hmmm....dats really gr8...since i started using excel in my work..i started off with 2007 ...so now i am pretty much used to it..

And in my last reply i wrote about just macro code for other work not about formula using in other workbook....m still under confusion about udf on ther workbook... I know that if i call a udf by =Personal.xlsb!myfunction than it will work...but about saving function in personal workbook is still freaking me out...!!..After i press record macro in personal what do i have to do next...do i need to just copy the entire code and then ctrl+s than stop recording and exit and start again..??? Will that work..??

 

[xladept]

Hi Rishi.ssh,

You don't need to do all that. You can set up your ribbon to include a section for macros. And then run the macro from the macro list. Or, with the macro showing just hit F5. If you do use a key macro, you only need to do it once - just record a "wrapper" - a key that does nothing and then when you edit it - just insert the macro name "AlphaNum" as I've done at the end of the program.

But, I'm not the one to give you directions on running 2010 since I've repudiated it long ago.

Perhaps you should post another thread and get a 2010 expert to help you going forward. I really just don't know 2010, or 2007 any more.

Good Luck,

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

The given macro dosent seems to be working..please ckeck once..its returning false..all time.

 

[xladept]

Hi Rishi,ssh

It works for me. Are you selecting Cell A1 before you start the macro?

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

 

[xladept]

Hi Rishi,ssh,

Since AplhaNum doesn't really do anything and since, by an earlier post,
you prefer to have functions on the worksheet - I've turned the macro
AlphaNum into a Function SymNum - it still needs IsAlpha and IsSpace so now you can just put it the cell where you want the result!

=SymNum(A1)

Here they are:

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
Function SymNum(A As Range) As String
Dim S As String, j As Integer, k As Integer
S = A
If S = "" Then Exit Function
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

SymNum = "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 + 4, Len(S) - 1)
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) - 1): 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

SymNum = Mid(S, j, 3 + k)

NextRecord:


End Function

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