Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
State names into abbreviations
I am looking for a way to turn state names into abbreviations. Ex. Texas into
TX |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
State names into abbreviations
Insert a new sheet named "List"
In A1:B50 enter state names and abbreviations Run this macro. Sub findandreplace() Dim Vals As Range Dim ws As Worksheet Dim R As Range Dim RR As Range Set Vals = Sheets("List").Range("A1:B50") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "List" Then Set R = ws.UsedRange For Each RR In R RR.Value = Application.VLookup(RR.Value, Vals, 2, False) Next RR End If Next ws End Sub Gord Dibben MS Excel MVP On Tue, 28 Jul 2009 12:30:03 -0700, jennie wrote: I am looking for a way to turn state names into abbreviations. Ex. Texas into TX |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
State names into abbreviations
On Tue, 28 Jul 2009 12:30:03 -0700, jennie
wrote: I am looking for a way to turn state names into abbreviations. Ex. Texas into TX If you want a worksheet formula, you can try this: =VLOOKUP(A1,{"Alabama","AL";"Alaska","AK";"Arizona ","AZ";"Arkansas","AR"; "California","CA";"Colorado","CO";"Connecticut","C T";"Delaware","DE"; "District of Columbia","DC";"Florida","FL";"Georgia","GA"; "Hawaii","HI";"Idaho","ID";"Illinois","IL";"Indian a","IN";"Iowa","IA";"Kansas","KS"; "Kentucky","KY";"Louisiana","LA";"Maine","ME";"Mar yland","MD";"Massachusetts","MA"; "Michigan","MI";"Minnesota","MN";"Mississippi","MS ";"Missouri","MO";"Montana","MT"; "Nebraska","NE";"Nevada","NV";"New Hampshire","NH"; "New Jersey","NJ";"New Mexic","NM";"New York","NY"; "North Carolina","NC";"North Dakota","ND";"Ohio","OH"; "Oklahoma","OK";"Oregon","OR";"Pennsylvania"," PA"; "Rhode","Island";"South Carolina","SC";"South Dakota","SD"; "Tennessee","TN";"Texas","TX";"Utah","UT";"Vermont ","VT"; "Virginia","VA";"Washington","WA";"West Virginia","WV"; "Wisconsin","WI";"Wyoming","WY"},2,FALSE) Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace state names with state code abbreviations | Excel Worksheet Functions | |||
Validation for state abbreviations | Excel Discussion (Misc queries) | |||
state abbreviations | New Users to Excel | |||
Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
US State Abbreviations List | New Users to Excel |