Formula that combines Contains and OR to search for values in one cell

BJIloveSmartSheet

Howdy!

I am new to smart sheet, and have the programming knowledge of a 1st grader :-)

I am attempting to write a formula that returns a specified value IF it finds ANY of the specified values in a cell (that's within the same row). I also want to know if the cell is NULL or if ANY of the values are NOT found.

For example:

Column 1, Row 1 cell contains the value: a b 4 8 2

Search in Column 1, Row 1, for any values that contain "4" or a or "2"

IF any one or more of these values are found, then:

Column 2, Row 1: = "Yellow"

OR

IF any of these values are NOT found AND the Cell is Not NULL, return "GREEN",

OR

IF the Cell is NULL, return "RED"

My Attempt to write this:

=IF(CONTAINS("a", Body1), OR (CONTAINS("D", Body1),"YELLOW", "GREEN")

How would I write this? I would appreciate any advice. Thank you so much.

James Keuning

If your row has no other values, the RED will not show up. Smaartsheet needs to see values in other cells, or the formula won't return its result.

=IF(LEN([Column1]@row) < 1, "RED", IF(OR(CONTAINS("4", [Column1]@row), CONTAINS("2", [Column1]@row)) = true, "yellow", "green"))