Display state abbreviation from full name

R

rhmartin

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.
 
J

Jacob Skaria

Try with data in cell A1
=SUBSTITUTE(A1,"NEWYORK","NY")

If this post helps click Yes
 
L

Luke M

Another option would be to create a lookup table somewhere, and use a LOOKUP
function (described in XL help file fully)
 
R

rhmartin

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.
 
J

Jacob Skaria

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
 
J

Jacob Skaria

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)
 
Top