Return a certain value if partial text is found on another cell

Joao Lima

Hello guys, I've been struggling to find a good formula for this case. Basically I have one column with different text (Band 5 RN, Band 6 RN and Band 2 HCA) and I want to create a new column that returns "Nursing" when the cell contains the text RN and to return "Other" when it doesn't.

Anyone with a correct formula? I've tried IF(CONTAINS) without success. Thanks!

Paul Newcome

Try this:

=IF(CONTAINS("RN", [1st Column Name]@row), "Nursing", "Other")

Joao Lima

Thank you Paul, I actually went around and used an IF(FIND()...

Nancy Heater

Hey @Paul Newcome … tagging onto this oldie but a goodie, what if I wanted to take this same premise, but needed to have (3) options and (3) results for it to populate.

Example: I want to look for the words "Apples", "Oranges" or "Bananas" in column1 and return the corresponding value of "Apples", Oranges" or "Bananas" in another column. I'm just not sure how to expand it to keep it going. Thx!

Paul Newcome

@Nancy Heater Can you provide a screenshot for context? If I understand correctly, you would use a series of IF statements "added" together (not nested).

=IF(CONTAINS("Apples", [Column Name]@row), "Apples" + CHAR(10), "") + IF(CONTAINS("Oranges", [Column Name]@row), "Oranges" + CHAR(10), "") + IF(CONTAINS("Bananas", [Column Name]@row), "Bananas", "")

Nancy Heater

Howdy @Paul Newcome!

This is what I was trying to do, but Im getting an "unparseable" error.

=IF(CONTAINS("Apples", Description@row), "Apples"), IF(CONTAINS("Oranges", Description@row), "Oranges"), IF(CONTAINS(Bananas", Description@row), "Bananas")

Nancy Heater

I figured it out @Paul Newcome ! I had to many ) included! Thanks for your guidance as always!