How to find a word and return a value
=IF([Trip Purpose]@row=(FIND"Morning"),"2Day",IF[Trip Purpose]=(FIND"Afternoon"),"3Swing",IF[Trip Purpose]=(FIND"Night"),"1Grave", "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 2Day, 3Swing,1Grave, or Float.
Best 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), "2Day", IF(CONTAINS("Afternoon", [Trip Purpose]@row), "3Swing", IF(CONTAINS("Night", [Trip Purpose]@row), "1Grave", "Float")))
Let me know how this works for you.
Answers

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), "2Day", IF(CONTAINS("Afternoon", [Trip Purpose]@row), "3Swing", IF(CONTAINS("Night", [Trip Purpose]@row), "1Grave", "Float")))
Let me know how this works for you.

@Marta Lewis's solution will work.
If you wanted to use the FIND function, you will need to keep in mind that it returns a numerical value based on Marta's info above.
So to use the find function, you would need to say that IF the FIND returns a value greater than 0 (which means that it is found somewhere in the string.
=IF(FIND("Morning", [Trip Purpose]@row) > 0, "2Day", .......................................................

Thank you @Marta Lewis!!

You're welcome, @Marla Berge.
Paul's FIND formula will work if your Trip Purpose column text will vary greatly, i.e. the placement of the words can change, enabled by Text/Number formatting. The CONTAINS formula will work best if the Trip Purpose column is standardized with Dropdown formatting, choice of specified phrases.

@Marta Lewis I actually prefer the CONTAINS function for cases like this. FIND is case sensitive whereas CONTAINS is not. CONTAINS is much more flexible because of that AND it is easier to use (in my opinion).
There are only 3 times where I personally will use a FIND function when searching through a string to see if specific text exists.
 I am helping someone who prefers to use the FIND function.
 I am searching a Contact type column. For some reason CONTAINS doesn't like that column type.
 I need my search to be case sensitive.
The only reason I provided a possible solution above using FIND was to show how to use what @Marla Berge had already started working with, but in all reality, I personally would have used CONTAINS.

Good to know. Thank you, @Paul Newcome.

Help Article Resources
Categories
Check out the Formula Handbook template!