How to find a word and return a value

Marla Berge

=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.

Marta Lewis

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.

Marta Lewis

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.

Paul Newcome

@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, "2-Day", .......................................................

Marla Berge

Thank you @Marta Lewis!!

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.

Paul Newcome

@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.

1. I am helping someone who prefers to use the FIND function.
2. I am searching a Contact type column. For some reason CONTAINS doesn't like that column type.
3. 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.

Marta Lewis

Good to know. Thank you, @Paul Newcome.

Paul Newcome

Sure thing. 👍️