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

Options

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

• ✭✭✭
Options

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"))

• Options

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

• ✭✭✭✭✭✭
edited 01/17/22
Options

I hope you're well and safe!

You'd have to use the HAS function instead.

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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.

• ✭✭✭
Options

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"))