Formula to display multi-select options separately

I want to use the same formula, but instead of adding number amounts, I want it to display multiple selections in another column.

Currently, tried this: =IF(CONTAINS("- Joining neighborhood-based, in-person adult learning house parties", [22/23: Torah (Learning) Commitment]@row), "Jewish Journey") + IF(CONTAINS("- Participating in holiday themed classes in preparation for upcoming Jewish holidays", [22/23: Torah (Learning) Commitment]@row), "Holiday Bootcamp")

Which it did work to display both short term options in the next column, but it is adding them together so there is no space and they aren't showing up as separate options. When exporting answers to Excel, I need these different terms to export as separate selections, not one answer. Right now, I manually select these short terms to match what was submitted. So with the current formula, it displays "Jewish JourneyHoliday Bootcamp" as one answer. But I want the formula to separate them out as separate answers if they are selected. The column the formula is in is a multi-select option. Is there a way to have the formula display multiple options selected so I don't have to manually select?

Tags:

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @Arianna Sikorski

    You should just be able to manually include a space in your formula as below:

    =IF(CONTAINS("- Joining neighborhood-based, in-person adult learning house parties", [22/23: Torah (Learning) Commitment]@row), "Jewish Journey") +" "+ IF(CONTAINS("- Participating in holiday themed classes in preparation for upcoming Jewish holidays", [22/23: Torah (Learning) Commitment]@row), "Holiday Bootcamp")

    Hope that helps

    Thanks

    Paul

  • @Paul McGuinness Thank you! Although the space did not work, I received another response with CHAR(10) recommendation that helped separate the terms the way I needed. However, the formula puts a "0" at the beginning of a term name if there is only one selected or on the last term of multiple selections.

    So if only one is selected, it looks like "0Holiday Camp". If it's multiple, it looks like "Holiday Camp Jewish Journey 0Prayer/Parsha Study"

    I tried putting a space at the end of the formula, but nothing changes. Is there a way to tell it not to include the zero since it's "adding" terms? Here is the current formula I am using:


    =IF(CONTAINS("- Learning in preparation for a lifecycle event (converting, adult Bnei Mitzvah, wedding, etc.)", [22/23: Torah (Learning) Commitment]@row), "Jewish Journey" + CHAR(10)) + IF(CONTAINS("- Participating in holiday themed classes in preparation for upcoming Jewish holidays", [22/23: Torah (Learning) Commitment]@row), "Holiday Bootcamp" + CHAR(10)) + IF(CONTAINS("- Participating in our online classes (e.g. Parsha Study (The Weekly Torah Reading), The Hows and Whys of Jewish Prayer)", [22/23: Torah (Learning) Commitment]@row), "Prayer/Parsha Study" + CHAR(10)) + IF(CONTAINS("- Participating in Family and/or Parent Learning", [22/23: Torah (Learning) Commitment]@row), "Family and Children's Programming" + CHAR(10)) + IF(CONTAINS("- Joining weekday Morning Minyan services on Zoom", [22/23: Torah (Learning) Commitment]@row), "Morning Minyan" + CHAR(10)) + IF(CONTAINS("- Joining neighborhood-based, in-person adult learning house parties", [22/23: Torah (Learning) Commitment]@row), "Adult Learning" + CHAR(10)) + IF(CONTAINS("- Participating in cohort-based learning series (by demographic or about particular themes)", [22/23: Torah (Learning) Commitment]@row), "Cohort Learning" + CHAR(10)) + " "

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/31/23

    Each of your IF statements needs a "blank" output in the value_if_false (third) portion.


    =IF(CONTAINS("..........", [22/23: Torah (Learning) Commitment]@row), "Jewish Journey" + CHAR(10), "") + IF(................


    EDIT: The bold formatting isn't showing up very well, but it is the "comma quote quote" right after CHAR(10).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!