Formula help - delimiter should not appear when cell is blank
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
-
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
-
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))), "; ")
-
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, "; ,", "")
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!