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

 

[xladept]

Hi rishi.ssh,

My routine quits when a null cell is encountered in the active cell row.

Is your data continuous or do you have a break?

Be sure to start the macro in a complete continuous row and it shouldn't stop until the end of data. If you need to have a break in the data, we can recode the way it quits.

 

[rishi.ssh]

No my row isint blank...and whenever it finds any 0 than it itself returns 0....The rows are continous...Anywayz....Could u please help me out with extracting Data from similar mails from outlook...??? This thread is sloved....All thankx with you..i do not feel necessary that u need to work on this macro actually....Please help me regarding extraction data from mail body from outlook...And sorry for replying late i was out of town for some days.

 

[xladept]

Hi rishi.ssh,

I can't help with outlook - I don't use it - sorry.

 

[rishi.ssh]

Ok i have extracted data from mail into excel...now i guess you can help me out...because i wanna extract two fields of data .... Buyer Id and Comission. which follows a patters similar in all mails just as i will show you below.Buyer ID: xxx-xxxxxxx-xxxxxxxCOMISSION: -$5.2 They are exactly in same patterN like if a macro can find "ID:" I i will began extraction leaving space than the value untill next space is encountered and post in into adjacent column against same cell... Also next it should also find "COMISSION:" AND start extracting value after a aspace till a space or till the value finishes..in another column..Like if data is in B2 ..ID Should come in C2 And Commission in D2....NOTE: EVERY ID CONSISTS OF 19 character no less no more including "-".Commission is of variable length Like -$5.2 or 15.28 or 124.80 ....Upto two decimle points...Please help...

 

[xladept]

Hi Rishi.ssh,

If the data are all in the same column then you can select the topmost cell and play this macro:

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 + 23, S, ":")
ActiveCell.Offset(0, 2) = Mid(S, j + 1, Len(S) - j + 1)
ActiveCell.Offset(1, 0).Select
ExtractIDComm
End Sub

 

[rishi.ssh]

@exadept...surely i will try...and Will it also extract commission: in column Other column against same Id..??

 

[rishi.ssh]

also commission: and id: can be anywhere....for example..Cell A will look likeThis is your new buyer information Bla bla.....

Buyer ID: 143-2222222-7777777

Buyet Name: Michele cohle

Address: xxxxxx xxxx

Other Information: Xyz
COMISSION: -$51.19

Then more other linesNow what i want in B2 is just the IDAND C2 just the commission in this case -$51.19R u in fb..?

 

[xladept]

Hi Rishi.ssh,

Maybe you could post a few examples, because I relied on the previous example:

[TABLE="width: 342"]
<tbody>[TR]
[TD="width: 342"]Buyer ID:   xxx-xxxxxxx-xxxxxxxCOMISSION: -$5.2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rishi.ssh]

I gave you example... Just it should extract content after ID: AND COMMISSION: from the cell..which can be any anywhere in the cell..

 

[xladept]

Hi Rishi.ssh,

Is there something after the comission? If so the macro must be amended.