Formula IF OR CONTAINS - Change State to State Abbreviation

kcaudill1989

I have a State Column and a State Abbreviation Column. The State column is a dropdown column that is filled out from a Form. I have a Formula in the State Abbreviation Column that looks at the State Column and then populates the appropriate State Abbreviation.

Here is my issue for West Virgina and Arkansas it populates both WV VA and AR KS. How can I get the formula to look at the Complete Text?

This is what I get:

This is my Formula:

=IF(OR(CONTAINS("Alabama", [State]@row)), " AL ", "")

+ IF(OR(CONTAINS("Alaska", [State]@row)), " AK ", "")

+ IF(OR(CONTAINS("Arkansas", [State]@row)), " AR ", "")

+ IF(OR(CONTAINS("Arizona", [State]@row)), " AZ ", "")

+ IF(OR(CONTAINS("California", [State]@row)), " CA ", "")

+ IF(OR(CONTAINS("Colorado", [State]@row)), " CO ", "")

+ IF(OR(CONTAINS("Connecticut", [State]@row)), " CT ", "")

+ IF(OR(CONTAINS("District of Columbia", [State]@row)), " DC ", "")

+ IF(OR(CONTAINS("Delaware", [State]@row)), " DE ", "")

+ IF(OR(CONTAINS("Florida", [State]@row)), " FL ", "")

+ IF(OR(CONTAINS("Georgia", [State]@row)), " GA ", "")

+ IF(OR(CONTAINS("Hawaii", [State]@row)), " HI ", "")

+ IF(OR(CONTAINS("Iowa", [State]@row)), " IA ", "")

+ IF(OR(CONTAINS("Idaho", [State]@row)), " ID ", "")

+ IF(OR(CONTAINS("Illinois", [State]@row)), " IL ", "")

+ IF(OR(CONTAINS("Indiana", [State]@row)), " IN ", "")

+ IF(OR(CONTAINS("Kansas", [State]@row)), " KS ", "")

+ IF(OR(CONTAINS("Kentucky", [State]@row)), " KY ", "")

+ IF(OR(CONTAINS("Louisiana", [State]@row)), " LA ", "")

+ IF(OR(CONTAINS("Massachusetts", [State]@row)), " MA ", "")

+ IF(OR(CONTAINS("Maryland", [State]@row)), " MD ", "")

+ IF(OR(CONTAINS("Maine", [State]@row)), "ME", "")

+ IF(OR(CONTAINS("Michigan", [State]@row)), " MI ", "")

+ IF(OR(CONTAINS("Minnesota", [State]@row)), " MN ", "")

+ IF(OR(CONTAINS("Missouri", [State]@row)), " MO ", "")

+ IF(OR(CONTAINS("Mississippi", [State]@row)), " MS ", "")

+ IF(OR(CONTAINS("Montana", [State]@row)), " MT ", "")

+ IF(OR(CONTAINS("New Hampshire", [State]@row)), " NH", "")

+ IF(OR(CONTAINS("New Jersey", [State]@row)), " NJ ", "")

+ IF(OR(CONTAINS("New Mexico", [State]@row)), " NM ", "")

+ IF(OR(CONTAINS("New York", [State]@row)), " NY ", "")

+ IF(OR(CONTAINS("North Carolina", [State]@row)), " NC ", "")

+ IF(OR(CONTAINS("North Dakota", [State]@row)), " ND", "")

+ IF(OR(CONTAINS("Nebraska", [State]@row)), " NE ", "")

+ IF(OR(CONTAINS("Nevada", [State]@row)), " NV ", "")

+ IF(OR(CONTAINS("Ohio", [State]@row)), " OH ", "")

+ IF(OR(CONTAINS("Oklahoma", [State]@row)), " OK ", "")

+ IF(OR(CONTAINS("Oregon", [State]@row)), " OR ", "")

+ IF(OR(CONTAINS("Pennsylvania", [State]@row)), " PA ", "")

+ IF(OR(CONTAINS("Rhode Island", [State]@row)), " RI ", "")

+ IF(OR(CONTAINS("South Carolina", [State]@row)), " SC ", "")

+ IF(OR(CONTAINS("South Dakota", [State]@row)), " SD", "")

+ IF(OR(CONTAINS("Tennessee", [State]@row)), " TN ", "")

+ IF(OR(CONTAINS("Texas", [State]@row)), " TX ", "")

+ IF(OR(CONTAINS("Utah", [State]@row)), " UT ", "")

+ IF(OR(CONTAINS("Virginia", [State]@row)), " VA ", "")

+ IF(OR(CONTAINS("Vermont", [State]@row)), " VT ", "")

+ IF(OR(CONTAINS("West Virginia", [State]@row)), " WV ", "")

+ IF(OR(CONTAINS("Wisconsin", [State]@row)), " WI ", "")

+ IF(OR(CONTAINS("Washington", [State]@row)), " WA ", "")

+ IF(OR(CONTAINS("Wyoming", [State]@row)), " WY ", "")

Jennifer Kurtz

hey there!

I don't think you need the CONTAINS - or, even the "OR" - if I I'm interpreting what you're trying to do correctly. :)

you could restructure your formula like,

=IF(State@row="Kansas", "KS", IF(State@row="Arkansas", "AR" ..

Another option you could try, especially if you are converting states to abbreviations in other places - would be to create a reference sheet with the full state names in one column and the abbreviations in the next. Then, you could use an INDEX(MATCH to look up the abbreviation from there. This might be simpler/faster than writing 50 IF statements. Let us know if you would like more help with doing something like that!