Can someone please help how this formula can work with the similar text?

Aatish Chaudhary

=IF(OR(CONTAINS("Work", [ID]@row)), "Right" , IF(OR( CONTAINS("Worklife", [ID]@row)), "Wrong",""))

Jeff M.

This should work for you. Will the cell contain more than the word "Work" or "Worklife"? Meaning are you searching for these words within a phrase?

=IF(ID@row = "Work", "Right", IF(ID@row = "Worklife", "Wrong"))

Aatish Chaudhary

Yes the cell contain a list of more than 50 words so I put it here just one word to keep the formula shorter. It looks like if I have a word "Work" in my list than I can not use any other word starting with Work... I hope there must be a way to fix this

Andrée Starå

Hi @Aatish Chaudhary

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

You'd have to use the HAS function instead.

https://help.smartsheet.com/function/has

Try something like this. (you'll have to change the column type to a multi-select kind if it isn't already)

=IF(HAS(ID@row, "Work"), "Right")

Did that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Aatish Chaudhary

Hi Andree,

Thanks for your reply. My actual formula is still 50x from this one =IF(OR(CONTAINS("Work", [ID]@row), CONTAINS("Task3", [ID]@row), CONTAINS("Task4", [ID]@row), CONTAINS("Case", [ID]@row)),"Work Category", IF(OR(CONTAINS("Worklife", [ID]@row), CONTAINS("Task1", [ID]@row), CONTAINS("Task2", [ID]@row)),"Task Category", IF(OR(CONTAINS("Case", [ID]@row), CONTAINS("Case ID", [ID]@row), CONTAINS("Case No", [ID]@row)),"Case Category",)))

This formula is working perfectly fine and solves the purpose the only thing is I have 3 words in my list similar to each outher i.e. Work, Worklife and in this case the formula is unable put the Worklife into Worklife's cetagory. Since the Worklife word match with the word Work it consider Workflife into Work's cetagory.

Is it the limitation in the smartsheet that we can not put the similar words in the list to categorize them differently. Even I tried to tweak the word like Work-Life, Work_life but nothing worked.

Jeff M.

The issue you are running into is that CONTAINS is returning all words that "contain" work. Andree suggested using the HAS function instead of the CONTAINS. HAS will search for the exact words, such as "work" & "worklife".

Also, with the HAS function, you can search a range of cells in a row, if needed. I included an example below. You should be able to keep nesting this formula by adding IF(HAS("ENTER WORD" to the end and repeating.

=IF(HAS("Work", ID@row:Date@row), "Right", IF(HAS("Worklife", ID@row:Date@row), "Wrong"))