Formula help - delimiter should not appear when cell is blank

jacquedale

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)

KPH

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, "; ,", "")

Carson Penticuff

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

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

@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.