Nested IF/OR Contains Function

Chloe Rose

Hi,

I'm trying to write a CONTAINS formula to auto-populate the project location column. There are 6 states to choose from. The Project Name is usually very long & descriptive but always contains the standard 2 letter code for the six states that the projects could be in. I came across the CONTAINS formula but cannot figure out how to write it so that it rolls through all of the options. Ex: If the Project Name column has a cell that contains "UT" it will spit out Utah in the Project Location column. If the Project Name has a WY in a cell it spits out Wyoming as the location etc. Each time I attempt to write the formula it gives me the Incorrect argument or the unparseable.

Thanks for any help!

Andrée Starå

Hi @Chloe Rose - 8PCC

Try something like this.

=IF(CONTAINS("CO", ColumnName:ColumnName, "Colorado", 
IF(CONTAINS("UT", ColumnName:ColumnName, "Utah")

Did it work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Chloe Rose - 8PCC

Try something like this.

=IF(CONTAINS("CO", ColumnName:ColumnName, "Colorado", 
IF(CONTAINS("UT", ColumnName:ColumnName, "Utah")

Did it work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jennifer Greenough

One alternative would be to create a column to split the state name from the project name (since it appears you use "-" as a delimiter"). See here for a handy guide on how to split text in Smartsheet. You could then use a VLOOKUP for the state using a secondary sheet. Training on VLOOKUP is available in the Learning Center here.

This might be a bit cleaner in terms of data management and result in fewer mismatches.

Jennifer