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!

andrea lafrania

@Jennifer Kurtz - I am trying to complete an Index formula on one of my sheets. Can you help ?? I would like to populate the state abbreviation based on what State Name they choose in one of my columns. I created a reference sheet, but for some reason I can't get the formula to work properly.

Jennifer Kurtz

@andrea lafrania - Hi there!

I'm guessing your reference sheet has a column with the full state name, and then one with the appropriate abbreviation — and then you're using a cross-sheet formula to pull in the abbreviation to your data sheet. Is that correct?

An Index/Match function combo works great for something like that. The basic syntax would look like this:

=Index({Range for the state abbrev on your reference sheet}, MATCH([Full state name is on the data sheet]@row, {Range on Reference Sheet for full state name}, 0))

The 0 above indicates that you're looking for an exact match. You could also use 1 if the range is sorted ascending or -1 if it's sorted descending….but I always use 0 so i don't need to worry about whether the sorting has changed! The { } brackets indicate a cross-sheet formula. When your creating this formula in the cell, you'll want to click the link in the window that says "reference another sheet," and then go and select the correct column on your reference sheet. Best practice is to name the range. :)

Does that help? Feel free to add a screenshot of your reference sheet or more details about the specific difficulty you're having.

Good luck!

andrea lafrania

I'm getting an #invalid value

And to answer your question - Yes, I have a referene sheet that has one column the full state name with the abbreviation in the column 2. I am trying to looik at my cell in the column that is my "Onsite/Worksite State" which right now populates the full state name.

=Index({Range for the state abbrev on your reference sheet}, MATCH([Full state name is on the data sheet]@row, {Range on Reference Sheet for full state name}, 0))