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]

Yes COMMISSION: and single space followed by the value...Also its the same with ID: then a space and The valueNOTE: for both It has ":" Attached with both the words... Followed by a single space than the Content i want... Till next space is encountered... ID is always of total 19 characters... And comission can be variable..

 

[xladept]

Hi Rishi.ssh,

This may do the trick:

Sub ExtractIDComm():
Dim i As Integer, j As Integer, k As Integer, S As String
S = ActiveCell
If S = "" Then Exit Sub
i = InStr(1, S, "ID:")
ActiveCell.Offset(0, 1) = Mid(S, i + 4, 19)
j = InStr(i + 23, S, "$")
k = InStr(j, S, " ")
If k Then
ActiveCell.Offset(0, 2) = Mid(S, j - 1, k - j + 1)
Else
ActiveCell.Offset(0, 2) = Mid(S, j - 1, Len(S) - j + 2)
End If
ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub

 

[rishi.ssh]

@ xladept the first part worked smoothly related to extraction of Id but Something wrong went with the second part for extracting The comission because like i said it has other data also like product price ship price all have $ so i told you about that "commission:" part which is unique and which could probably help in extraction of the exact value...please consider this..thanks

 

[xladept]

Try this,


Sub ExtractIDComm(): Dim i As Integer, j As Integer, k As Integer, S As String
S = ActiveCell If S = "" Then Exit Sub
i = InStr(1, S, "ID:") ActiveCell.Offset(0, 1) = Mid(S, i + 4, 19)
j = InStr(1, S, "$") k = InStr(j, S, " ") If k Then
ActiveCell.Offset(0, 2) = Mid(S, j - 1, k - j + 1)
Else ActiveCell.Offset(0, 2) = Mid(S, j - 1, Len(S) - j + 2)
End If ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub

Instead of searching from i+23 for the dollar sign - start at one!

 

[rishi.ssh]

Ok..i will try this and let you know...but as it it seen you are again using search of $ sign... Which in this case of mine i fear will not work...anywayz i am positive towards your work..hope it works well thankx.

 

[rishi.ssh]

.

 

[rishi.ssh]

Hey i am sorry to inform you that the id is well extracted but the comission part is getting something wrong... Here i found out something might be if can put this in a macro..it will help me..=TRIM(MID(A1,FIND("^",A1)+1,FIND(" ",A1,FIND("^",A1))-FIND("^",A1))) can u use this formula in this macro to extract anything in a cell after a ^?? Till next space is encountered in column c.. I mean it sould take from the column i select.not just A1...i hope this u can do. Thankx

 

[xladept]

Hi rishi.ssh,

There isn't any caret in your example - try this:

Sub ExtractIDComm(): Dim i As Integer, j As Integer, S As String
S = ActiveCell
If S = "" Then Exit Sub
i = InStr(1, S, "ID:")
ActiveCell.Offset(0, 1) = Mid(S, i + 4, 19)
j = InStr(i, S, "$")
ActiveCell.Offset(0, 2) = Mid(S, j - 1, Len(S) - j + 2)
ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub

 

[rishi.ssh]

Yes there wasnt any character in my example..but the thought if i replace all the "commission: " with "^" before i run d macro then be it will be easy for extraction ...anything after a "^"..because the space after commission: will no longer will be a problem..and can extract easily untill next space is encountered..but i will definately try your Last code..and can i add u in fb..?if u dnt mind..i will show u the sample file also..for your easy convenience.

 

[xladept]

Hi rishi.ssh,

I've added another index:

Sub ExtractIDComm(): Dim i As Integer, j As Integer, S As String
Dim k As Integer
S = ActiveCell
If S = "" Then Exit Sub
i = InStr(1, S, "ID:")
ActiveCell.Offset(0, 1) = Mid(S, i + 4, 19)
j = InStr(1, S, "$"): k = InStr(j, S, " ")
If k Then
ActiveCell.Offset(0, 2) = Mid(S, j - 1, k - j + 1)
Else
ActiveCell.Offset(0, 2) = Mid(S, j - 1, Len(S) - j + 2)
End If
ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub