Formula IF OR CONTAINS - Change State to State Abbreviation
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 ", "")
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!