Excel - state abbreviations

Asked By gls858

18-Jun-07 02:32 PM

Does anyone have a way of converting state abbreviations to the full
state name? Example AZ to Arizona. I have two lists I need to sort by
state and compare. One list has the abbreviation and the other the full
name. Of course they don't sort the same.

gls858

18-Jun-07 02:46 PM

There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usps_abbreviations.html

They're the wrong way round for what you want but that's simply a matter of
manually reversing the columns. Then

=VLOOKUP("AZ",A1:B59,2,FALSE)

AZ could of coyrse be a cell reference

Mike

18-Jun-07 04:13 PM

Thanks Mike. I'll have to give it a try. Just got something else thrown
on my desk so it may be a while before I get back to the lists. I'll
try and post back to let you know if I got it to work.

gls858

19-Jun-07 06:45 PM

Thanks for the help Mike I finally got around to looking at this
and I can't seem to get VLOOKUP to do what I want. I'll try a more
detailed explanation of what I would like to do. Maybe that will help.
I have a list of names with address and the state is represented by
it's two letter abbreviation with multiple entries for each state
(over 500) like so:
AZ
AZ
AZ
AR
AR
AR
CA
CA


I would like to add another column with a formula that would change the
abbreviation to the full name like so:
Arizona
Arizona
Arizona
Arkansas
Arkansas
Arkansas
California
California

Is this possible with Vlookup?

gls858

19-Jun-07 06:59 PM

Assuming the following:

Abbreviations in K1:K50
Full Names in L1:L50

Your state abbreviations in A1:A50

use

=VLOOKUP(A1,$K$1:$L$50,2,FALSE)

to return the full name of the state whose abbreviation is in your data in
cell  A1.


--
Cordially,
Chip Pearson
Microsoft MVP  - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

20-Jun-07 10:50 AM

Thanks Chip! Worked like a charm. I am assuming the absolute value causes
the lookup to start at the top of the coll each time.

gls858

20-Jun-07 11:03 AM

Yes. Since the lookup range has absolute references, it will not change as
you copy/fill the formula down a column. The lookup value has a relative
range so that it will change as you fill down.


--
Cordially,
Chip Pearson
Microsoft MVP  - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

20-Jun-07 04:34 PM

Thanks for the explanation.

gls858