State names into abbreviations
 

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

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

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