Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

State names into abbreviations

332 views
Skip to first unread message

jennie

unread,
Jul 28, 2009, 3:30:03 PM7/28/09
to
I am looking for a way to turn state names into abbreviations. Ex. Texas into
TX

Gord Dibben

unread,
Jul 28, 2009, 3:46:21 PM7/28/09
to
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

unread,
Jul 28, 2009, 4:25:50 PM7/28/09
to
On Tue, 28 Jul 2009 12:30:03 -0700, jennie
<jen...@discussions.microsoft.com> wrote:


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

Josh Kirk

unread,
Feb 9, 2022, 5:37:34 PM2/9/22
to
Some minor fixes to New Mexico and Rhode Island:

=VLOOKUP(B2,{"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 Mexico","NM";"New York","NY";"North Carolina","NC";"North Dakota","ND";"Ohio","OH";"Oklahoma","OK";"Oregon","OR";"Pennsylvania","PA";"Rhode Island","RI";"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)
0 new messages