How to find a word and return a value


=IF([Trip Purpose]@row=(FIND"Morning"),"2-Day",IF[Trip Purpose]=(FIND"Afternoon"),"3-Swing",IF[Trip Purpose]=(FIND"Night"),"1-Grave", "Float")

I'm trying to find the work Morning, Afternoon, or Night from a phrase in a cell in the Trip Purpose Column and then make the new column (Shift/) to return the corresponding value 2-Day, 3-Swing,1-Grave, or Float.

Best Answer

  • Marta Lewis
    Marta Lewis ✭✭
    Answer ✓

    FIND is used to return the starting point of a specified character within a string of text. You may want to try CONTAINS instead, something like this:

    =IF(CONTAINS("Morning", [Trip Purpose]@row), "2-Day", IF(CONTAINS("Afternoon", [Trip Purpose]@row), "3-Swing", IF(CONTAINS("Night", [Trip Purpose]@row), "1-Grave", "Float")))

    Let me know how this works for you.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!