Display state abbreviation from full name

  • Thread starter Thread starter rhmartin
  • Start date Start date
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.
 
Try with data in cell A1
=SUBSTITUTE(A1,"NEWYORK","NY")

If this post helps click Yes
 
Another option would be to create a lookup table somewhere, and use a LOOKUP
function (described in XL help file fully)
 
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.
 
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
 
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)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Validation for state abbreviations 2
Converting State Names to State Abbreviations 1
How do I abbreviate words? 2
changeing words conditional?? please help 3
Pivot Table: Remove old field cell value 1
Extracting abbreviated days, converting to full word 4
Populating Cells with Table Data 1
Enter text in a cell to return a text value in same cell 6

Back
Top