Display state abbreviation from full name

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Luke M]

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

 

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

 

[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

 

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