State names into abbreviations

[jennie]

I am looking for a way to turn state names into abbreviations. Ex. Texas into
TX

 

[Gord Dibben]

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

 

[Lars-Åke Aspelin]

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","CT";"Delaware","DE";
"District of Columbia","DC";"Florida","FL";"Georgia","GA";
"Hawaii","HI";"Idaho","ID";"Illinois","IL";"Indiana","IN";"Iowa","IA";"Kansas","KS";
"Kentucky","KY";"Louisiana","LA";"Maine","ME";"Maryland","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