U.S State abbreviation to full state name

rishi.ssh

Board Regular
Joined
May 4, 2012
Messages
60
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
 
Thank you very much for your replies..i very much appreciate your hard work and yes i agree that VBA macro arent better than Formulas...but this are little complicated at my end so i asked for that...if i had a formula i could have used instead of this.
The issue of states is now solved :) Thabk u very very much.

But the second issue is still there and its making my head heated up...i tried above given macro..but it did not worked..i will be kind enough if u could try something out to get me out from this issues..thanks..i will show u some examples now..all are there in A1,A2,A3,A4,A5...and so on..also i will give u example as what exactly i need in column B1,B2,B3,B4,...AND SO ON...

A1=BOS-89|SO#6150051/B315-BE|6150051|BOS-89
A2=PCY-677|82868796
A3=RIC-534/89508|94161/ONE TOUCH PETGATE 150 OW
A4=AZP1012|CET-172
A5=WIN-5222|REF: 001560349 ITEM: 89999|89999|001560349|10906165
A6=O000272093-2:1|00000902341032136251|EC4300-EC3|103213625 | OST-1016|


Now i result which i need is something like this
in cloumn b

B1=BOS-89
B2=PCY-677
B3=RIC-534
B4=CET-172
B5=WIN-5222
B6=OST-1016




Thankx in advance..waiting for an effective and positive reply.have a nice day.:)
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
The following UDF (user defined function) was taken from this "mini" blog article of mine...

Find a text substring that matches a given "pattern"

(you might want to read the article to see how to use the function)...

Code:
Function GetPattern(Source As String, ByVal Pattern As String) As String
  Dim X As Long, FindPattern As Long
  Do Until Left(Pattern, 1) <> "*"
    Pattern = Mid(Pattern, 2)
  Loop
  For X = 1 To Len(Source)
    If Mid(Source, X) Like Pattern & "*" Then
      FindPattern = X
      Exit For
    End If
  Next
  For X = 1 To Len(Source) - FindPattern + 1
    If Mid(Source, FindPattern, X) Like Pattern Then
      GetPattern = Mid(Source, FindPattern, X)
      Exit For
    End If
  Next
End Function
After you install the above UDF (see instructions below), you can put this formula in B1 and copy it down...

=TRIM(SUBSTITUTE(GetPattern(A1&"|","[A-Z][A-Z][A-Z]-#*|"),"|",""))

Now, the above formula may give you the wrong result in cell B3 because I assumed the slash after the 584 number was supposed to have been a pipe symbol (to match the symbolling used throughout the rest of your data). If, however, that slash was correct, then you will need to use this formula instead...

=TRIM(SUBSTITUTE(GetPattern(SUBSTITUTE(A1,"/","|")&"|","[A-Z][A-Z][A-Z]-#*|"),"|",""))


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 the UDF just like it was a built-in Excel function.
 
Upvote 0
Hmm...i will surely try that out...tomm...i hope that works..and gives me exactly what i want..till now i assume i have preetly well explained you what i need ..
Thankx.
 
Upvote 0
This seems to do it, but it follows what you initially said about the string to be extracted which is abc-123.

Code:
Sub test5()
On Error Resume Next
Dim r As Range, cel As Range
Set r = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Dim str
Dim iCol As New Collection

For Each cel In r
str = Split(cel.Value, "|")
    For i = 0 To Application.WorksheetFunction.CountA(str) - 1
        If InStr(1, str(i), "-") > 1 And Len(str(i)) = 7 Then
            iCol.Add str(i), str(i)
        End If
    Next i
Next cel
Set cel = Range("B" & Rows.Count).End(xlUp).Offset(1)
For i = 1 To iCol.Count
    cel.Value = Trim(iCol(i))
    Set cel = cel.Offset(1)
Next i
End Sub
 
Upvote 0
Ok..i will try that too.....could you please if u dont mind make some corrections to it... As ABC-1234 CAN THE NUMERICAL PORTION BE ADJUSTED IN CODE TO TAKE BOTH 4FOUR DIGITS AND THREE...??
 
Upvote 0
Ok..i will try that too.....could you please if u dont mind make some corrections to it... As ABC-1234 CAN THE NUMERICAL PORTION BE ADJUSTED IN CODE TO TAKE BOTH 4FOUR DIGITS AND THREE...??
Did you try the code I posted? It should be able to handle any number of digits after the 3 letters and dash... that is because the code keys in on the pipe symbol as a delimiter.
 
Upvote 0
Hello Rishi.ssh,

If you put the state abbreviations in column 1 and their fullnames in column 2 on a sheet named StateNames which you can then hide then here's a function to get the fullname:

Code:
Function StateName(A As String) As String
Dim ThisSheet As String: ThisSheet = ActiveSheet.Name
Sheets("StateNames").Activate
On Error GoTo EndFunction
StateName = Cells(Cells.Find(A).row, 2)
EndFunction: End Function

You can't do one thing. XLAdept
 
Upvote 0
Oops!

Code:
Function StateName(A As String) As String
Dim ThisSheet As String: ThisSheet = ActiveSheet.Name
Sheets("StateNames").Activate
On Error GoTo EndFunction
StateName = Cells(Cells.Find(A).row, 2)
EndFunction: Sheets(ThisSheet).Activate: End Function
 
Upvote 0
Did you try the code I posted? It should be able to handle any number of digits after the 3 letters and dash... that is because the code keys in on the pipe symbol as a delimiter.
I will try tomm and will let you know...asap. :) hope what i need i get.
 
Upvote 0
<table style="border-collapse: collapse; width: 161pt;" width="214" border="0" cellpadding="0" cellspacing="0"><col style="width: 161pt;" width="214"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 161pt;" width="214" height="20">CLA-568|001</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CLA-595|001</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CLA-591|001</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-926|O000270552-2:1|00000902341031942846|WB424|103194284</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-925|O000270550-2:1|00000902341031942815|EM39800-6|103194281</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-923|O000270615-2:1|00000902341031942891|EM39800-6|103194289</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-922|O000270583-2:1|00000902341031942860|MET88RD|103194286</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-922|O000270583-2:1|00000902341031942853|MET88RD|103194285</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-922|O000270583-2:1|00000902341031942884|MET88RD|103194288</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-926|O000270552-2:1|00000902341031942839|WB432|103194283</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-924|O000270621-2:1|00000902341031942907|XT59ES|103194290</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-922|O000270583-2:1|00000902341031942877|MET88RD|103194287</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-926|O000270552-2:1|00000902341031942822|WB424|103194282</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">1852068|NWC-677</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033763</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033777</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033753</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033776</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033762</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WBX033768</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ABC-52</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">423933|BTC-510</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">423930|BTC-509</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1214/W173|59817.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1208/W172|59759.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1210/W172|59751.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1212/W172|59776.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1215/W173|59818.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1209/W172|59760.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1213/W172|59780.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TAC1211/W172|59771.00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JET-78|SO0245514</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JET-80|SO0245639</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">JET-79|SO0245515</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VNT-230|0187280 VT-CORKSCREW</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VNT-231|0187281 EP CORKEL 1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">14189|TKA346|TKA346|2012-04-24 06:52:54.740</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">14188|TKA345|TKA345|2012-04-24 06:53:41.423</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">521706|JFI- 767</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">521704|JFI- 766</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">551615|R-V513|R-V513 STORAGE BOX|VFH-151|-</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BOS-76|SO#6147723/B16240-BK|6147723|BOS-76</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BOS-76|SO#6147723/B16240-BK|6147723|BOS-76</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BOS-76|SO#6147723/B16240-BK|6147723|BOS-76</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CR42-PA|893|SG11100O_8860</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">CR92|890|SG11100O_8859</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">0119112|SPT-3562|0117560</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">0119117|SPT-3563|0117575</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">0119177|SPT-3564|0117630</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">FSG-90</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">647073|CHF-73</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PCY-669|82848365</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PCY-671|82850424</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PCY-672|82850425</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PO: CAP-50|CUST#:NY0127, SO#:20576092</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PO: CAP-53|CUST#:NY0127, SO#:20576094</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PO: CAP-51|CUST#:NY0127, SO#:20576093</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PO: 8232|CUST#:CA0410, SO#:20576068</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PO: CAP-49|CUST#:NY0127, SO#:20576091</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-902|O000270246-2:1|00000902341031886485|MRG12S-ESP|103188648</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">OST-919|O000270368-2:1|00000902341031914072|WST07|103191407</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">970762|970762</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">970761|970761</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VVR-40</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VVR-41</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">VVR-42</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TEX-32|447230-00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">TEX-31|447229-00</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PL:3374619(PO:MND359)|4839(1)</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2568|60449</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2569|60450</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2570|60449</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2574|60450</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2572|60370</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2567|02054</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2566|02126</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">WTL-2571|60449</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BLR-1304|672140</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">BLR-1305|672141</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">RIC-519/89078|94117/WOOD PETPEN 90-60</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">972819|ADS-77</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">972818|ADS-76</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">12-86919SO|PO WAP-119*


ITs something like this...

Pleae help..without changing other data in other columns..i mean whats against A! should be same and for all data must be same..
</td> </tr> </tbody></table>
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top