Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using named ranges for formula abbreviations | Excel Worksheet Functions | |||
Where can i find abbreviations for use in excel formulas | Excel Discussion (Misc queries) | |||
Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
create a list box in one cell of state abbreviations | Excel Worksheet Functions | |||
US State Abbreviations List | New Users to Excel |