Formula to pull selected text from a cell into a dropdown list.

ker9

@Paul Newcome - Thank you! I got bogged down in trying to join/collect/contain, simpler is always better.

Paul Newcome

Happy to help. 👍️

damon.tackett

@Paul Newcome @ker9 thank you so much! It is so easy to look right past the easy answer and try to make a complex formula. This seems to be working GREAT!

damon.tackett

@Paul Newcome is there an easy tag to the formula that would return "N/A" if none of the criteria matches? I don't want a string of N/As, just one.

Paul Newcome

@damon.tackett It will LOOK like a much larger formula, but really the syntax is pretty basic. We would essentially say

=IF(original_formula <> "", original_formula, "N/A")

If the original formula is not blank, then output the original formula. Otherwise output "N/A".

=IF(IF(CONTAINS("BT1", [Customer Order]@row), "BT1" + CHAR(10), "") + IF(CONTAINS("BT2", [Customer Order]@row), "BT2" + CHAR(10), "") <> "", IF(CONTAINS("BT1", [Customer Order]@row), "BT1" + CHAR(10), "") + IF(CONTAINS("BT2", [Customer Order]@row), "BT2" + CHAR(10), ""), "N/A")

ker9

@Paul Newcome @damon.tackett

Another method to check if the cell does not contain "BT"

=IF(CONTAINS("BT", [Customer Order]@row) = false, "N/A", IF(CONTAINS("BT1", [Customer Order]@row), "BT1" + CHAR(10), "") + IF(CONTAINS("BT2", [Customer Order]@row), "BT2" + CHAR(10), "") + IF(CONTAINS("BT3", [Customer Order]@row), "BT3" + CHAR(10), "") + IF(CONTAINS("BT4", [Customer Order]@row), "BT4" + CHAR(10), ""))

damon.tackett

I would rather those little tags for the longer formula than the alternative I was looking at of adding more columns and doing some kind of combine. Thank you again @Paul Newcome! And thank you @ker9 for calling in "the big guns"!!