Looking for a wildcard alternate solution

Options

Hi there,

 

I have a formula that I would usually use wildcards for, but wildcards don’t seem to work in Smartsheet, so I am hoping for an alternate solution.

 

This is my formula and an explanation on what it does;

 

=IF(OR([Column B]@row = "Word 1", [Column B]@row = "Word 2", [Column B]@row = "Word 3"), [Start Date]@row + 64, [Start Date]@row + 32)

 

This formula is in a due date column. It automatically determines the due date as 32 calendar days forward, unless Column B has one of three predetermined words in it, then it counts forward 64 days instead.

This formula works perfectly if Column B has only one word/phrase in it. It is a multi-select drop down column and sometimes there is more than one word/phrase selected. When this occurs, it doesn’t count forward the 64 days and counts forward the 32 days, as it is looking for an exact match. I tried *wildcards* and found this feature is not supported.

Any suggestions on how I could make this work?

 

Thanks in advance

Best Answer

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓
    Options

    Hi,

    This method should work for you when searching for a word within a string, or multi-select scenarios. Hope that helps!

    =IF(OR(CONTAINS("Word 1", [Column B]@row), CONTAINS("Word 2", [Column B]@row), CONTAINS("Word 3", [Column B]@row)), [Start Date]@row + 64, [Start Date]@row + 32)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!