state abbreviations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gls858 - 18 Jun 2007 19:32 GMT

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

Reply to this Message

Mike H - 18 Jun 2007 19:46 GMT

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

> 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

Reply to this Message

gls858 - 18 Jun 2007 21:13 GMT

> There's a noce sorted list here
>
[quoted text clipped - 15 lines]
>>
>> gls858

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

Reply to this Message

gls858 - 19 Jun 2007 23:45 GMT

> There's a noce sorted list here
>
[quoted text clipped - 15 lines]
>>
>> gls858

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

Reply to this Message

Chip Pearson - 19 Jun 2007 23:59 GMT

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.

Signature

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

>> There's a noce sorted list here
>>
[quoted text clipped - 45 lines]
>
> gls858

Reply to this Message

gls858 - 20 Jun 2007 15:50 GMT

> Assuming the following:
>
[quoted text clipped - 9 lines]
> to return the full name of the state whose abbreviation is in your data
> in cell A1.

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

gls858

Reply to this Message

Chip Pearson - 20 Jun 2007 16:03 GMT

> I'm assuming the absolute value causes
> the lookup to start at the top of the coll each time.

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.

Signature

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

>> Assuming the following:
>>
[quoted text clipped - 14 lines]
>
> gls858

Reply to this Message

gls858 - 20 Jun 2007 21:34 GMT

>> I'm assuming the absolute value causes
>> the lookup to start at the top of the coll each time.
>
> 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.

Thanks for the explanation.

gls858

Reply to this Message

Rate this thread: