Formula help - delimiter should not appear when cell is blank

Options

I am trying to build a formula that combines items from cells, but the cells may not all contain data each time. For example when the form is completed the user should indicate "# of Attendees". They would then enter the name and company name of each of the 5 possible attendees. I then need to combine all attendees in a generate report. I have it set as follows:

=[Attendee 1]@row + ", " + [Attendee 1 Company]@row + "; " + [Attendee 2]@row + ", " + [Attendee 2 Company]@row + "; " + [Attendee 3]@row + ", " + [Attendee 3 Company]@row + "; " + [Attendee 4]@row + ", " + [Attendee 4 Company]@row

However, when there are less than 5 attendees entered the delimiter still appears. Is there a way to only show delimiter when data is present?


A snippit is shown below for greater clarity (hopefully)




Jacque Smith

Project Controls, MSR-FSR

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You could wrap the entire formula in a SUBSTITTUTE function to replace semi colon followed by space followed by comma with nothing

    The syntax is

    =SUBSTITUTE(text to look in - aka your formula, text to find, text to replace it with).

    So for you this would be

    =SUBSTITUTE([Attendee 1]@row + ", " + [Attendee 1 Company]@row + "; " + [Attendee 2]@row + ", " + [Attendee 2 Company]@row + "; " + [Attendee 3]@row + ", " + [Attendee 3 Company]@row + "; " + [Attendee 4]@row + ", " + [Attendee 4 Company]@row, "; ,", "")

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    Instead of manually joining each entry, you can use a JOIN(COLLECT()) to collect all cells that are not blank and join them with your chosen delimiter.

    =JOIN(COLLECT([Attendee 1]@row:[Attendee 5 Company]@row, [Attendee 1]@row:[Attendee 5 Company]@row, NOT(ISBLANK(@cell))), "; ")

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You could wrap the entire formula in a SUBSTITTUTE function to replace semi colon followed by space followed by comma with nothing

    The syntax is

    =SUBSTITUTE(text to look in - aka your formula, text to find, text to replace it with).

    So for you this would be

    =SUBSTITUTE([Attendee 1]@row + ", " + [Attendee 1 Company]@row + "; " + [Attendee 2]@row + ", " + [Attendee 2 Company]@row + "; " + [Attendee 3]@row + ", " + [Attendee 3 Company]@row + "; " + [Attendee 4]@row + ", " + [Attendee 4 Company]@row, "; ,", "")

  • jacquedale
    jacquedale ✭✭✭✭✭
    Options

    @KPH This worked perfectly! Thank you so much you are a lifesaver. I was racking my brain and tried every variation/option I thought possible.


    Jacque Smith

    Project Controls, MSR-FSR

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!