Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default state abbreviations

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,501
Default state abbreviations

There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usp...eviations.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
"gls858" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default state abbreviations

Mike H wrote:
There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usp...eviations.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
"gls858" wrote:

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

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
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default state abbreviations

Mike H wrote:
There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usp...eviations.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
"gls858" wrote:

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


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
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7,247
Default state abbreviations

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)


"gls858" wrote in message
...
Mike H wrote:
There's a noce sorted list here

http://www.usps.com/ncsc/lookups/usp...eviations.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
"gls858" wrote:

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


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




  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default state abbreviations

Chip Pearson wrote:
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.


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
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7,247
Default state abbreviations


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.


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

"gls858" wrote in message
...
Chip Pearson wrote:
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.


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


  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default state abbreviations

Chip Pearson wrote:

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using named ranges for formula abbreviations Dave Breitenbach Excel Worksheet Functions 3 February 26th 07 06:21 PM
Where can i find abbreviations for use in excel formulas RG Excel Discussion (Misc queries) 3 January 27th 07 08:05 PM
Converting State Names to State Abbreviations aznate Excel Discussion (Misc queries) 1 October 20th 06 06:52 AM
create a list box in one cell of state abbreviations spitcher Excel Worksheet Functions 1 March 27th 06 11:01 AM
US State Abbreviations List DTTODGG New Users to Excel 1 November 16th 05 02:36 PM


All times are GMT +1. The time now is 08:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"