Help on Or/Contains formula

James Wesley

I am in the need of some help as I am banging my head against the wall trying different formula options here.

I wish to grab all characters left of a certain character combination (" -") from a drop-down cell. For instance, I wish to grab "Hosted Event (physical)" from the drop-down cell with "Hosted Event (physical) - Regional". This is the formula I am working with...what I am missing:

=IF (OR (CONTAINS ("Hosted", [Channel + Channel Tag]@row), CONTAINS ("Sponsored", [Channel + Channel Tag]@row), CONTAINS ("Webinar", [Channel + Channel Tag]@row)), LEFT ( FIND (" -", [Channel + Channel Tag]@row -20)), [Channel + Channel Tag]@row)

Thanks for your keen eyes!! Cheers, James

Paul Newcome

Try this:

=LEFT([Channel + Channel Tag]@row, FIND(" -", [Channel + Channel Tag]@row) -1)

James Wesley

Thanks Paul for chiming in! I plugged that into the larger formula, but unfortunately still "#UNPARSEABLE". FYI, the field header name had to change, so it's slightly different now.

=IF (OR (CONTAINS ("Hosted", [Channel + Channel Category]@row), CONTAINS ("Sponsored", [Channel + Channel Category]@row), CONTAINS ("Webinar", [Channel + Channel Category]@row)), LEFT([Channel + Channel Category]@row, FIND(" -", [Channel + Channel Category]@row) -1))

Paul Newcome

Lets try cleaning up the spaces and parenthesis.

=IF(OR(CONTAINS("Hosted", [Channel + Channel Category]@row), CONTAINS("Sponsored", [Channel + Channel Category]@row), CONTAINS("Webinar", [Channel + Channel Category]@row)), LEFT([Channel + Channel Category]@row, FIND(" -", [Channel + Channel Category]@row) -1))

James Wesley

THANK YOU! I didn't see any parenthesis changes, but this worked. Crazy that spaces would impact this.

Now would this statement continue to hold true if LEFT was replaced by RIGHT? Cheers!!

Paul Newcome

For the right, you would need to use

RIGHT([Channel + Channel Category]@row, LEN([Channel + Channel Category]@row) - (FIND(" -", [Channel + Channel Category]@row) + 2))

or

SUBSTITUTE([Channel + Channel Category]@row, LEFT([Channel + Channel Category]@row, FIND(" -", [Channel + Channel Category]@row) -1), "", 1)