| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display state abbreviation from full name
I need a formula that will replace the full name of a state with it's
abbreviation after importing data from a source I can't alter. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display state abbreviation from full name
Try with data in cell A1
=SUBSTITUTE(A1,"NEWYORK","NY") If this post helps click Yes --------------- Jacob Skaria "rhmartin" wrote: I need a formula that will replace the full name of a state with it's abbreviation after importing data from a source I can't alter. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display state abbreviation from full name
If you have a list of states and its abbreviations in Sheet2 say colA and B
try the below instead If A1 is having state; insert an additional column and use the below formula. =VLOOKUP(A1,Sheet2!A:B,2,FALSE) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try with data in cell A1 =SUBSTITUTE(A1,"NEWYORK","NY") If this post helps click Yes --------------- Jacob Skaria "rhmartin" wrote: I need a formula that will replace the full name of a state with it's abbreviation after importing data from a source I can't alter. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display state abbreviation from full name
This will work for a few cells, but I have multiple states and multiple
cells. I am looking for a way to use the full list of states and abbreviations, and compare to every cell I import with a State's name in it. For example, if cell A1 is New York, the formula would change it to NY, but if cell A1 is Rhode Island, the answer would be RI. "Jacob Skaria" wrote: Try with data in cell A1 =SUBSTITUTE(A1,"NEWYORK","NY") If this post helps click Yes --------------- Jacob Skaria "rhmartin" wrote: I need a formula that will replace the full name of a state with it's abbreviation after importing data from a source I can't alter. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display state abbreviation from full name
If you have a list of states and its abbreviations in Sheet2 say colA and B
try the below instead If A1 is having state; insert an additional column and use the below formula. =VLOOKUP(A1,Sheet2!A:B,2,FALSE) -- If this post helps click Yes --------------- Jacob Skaria "rhmartin" wrote: This will work for a few cells, but I have multiple states and multiple cells. I am looking for a way to use the full list of states and abbreviations, and compare to every cell I import with a State's name in it. For example, if cell A1 is New York, the formula would change it to NY, but if cell A1 is Rhode Island, the answer would be RI. "Jacob Skaria" wrote: Try with data in cell A1 =SUBSTITUTE(A1,"NEWYORK","NY") If this post helps click Yes --------------- Jacob Skaria "rhmartin" wrote: I need a formula that will replace the full name of a state with it's abbreviation after importing data from a source I can't alter. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display state abbreviation from full name
Another option would be to create a lookup table somewhere, and use a LOOKUP
function (described in XL help file fully) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rhmartin" wrote: I need a formula that will replace the full name of a state with it's abbreviation after importing data from a source I can't alter. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I show state-by-state data (as silos) on a map of NA | Charts and Charting in Excel | |||
| How can I control the full screen display width ? | Excel Discussion (Misc queries) | |||
| Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
| spell out state name from an abbreviation | Excel Discussion (Misc queries) | |||
| Worksheet won't display full screen | New Users to Excel | |||
Hybrid Mode
