How to JOIN multiple columns without delimiters
Here is my JOIN function used on Name, Start Time and Finish Time columns that works fine. However, I want to use it with IF empty show nothing (currently it shows the delimiters).
=JOIN(Performer1 + JOIN(": " + JOIN(Start1:Finish1, " - ")))
Any suggestion please?
Answers
-
You could use an IF AND or IF OR before the JOIN to only JOIN where at least one of the three columns is populated or where all are populated (therefore nothing will happen if all three (or any one, depending on how you get up the IF) are blank and there will be no delimiters).
Or, you could use IF for the string of delimiters instead of IF ISBLANK and return blank.
-
Assuming your set up looks something like this:
If you want the join only if Performer is not blank, try this formula in the Join column:
=IF(ISBLANK(Performer@row), "", JOIN(Performer@row + ":" + JOIN(Start@row:Finish@row, "-")))
The results would look like this:
If you want the join only if the Perfomer, Start AND Finish columns are populated, use this formula in the Join column:
=IF(OR(ISBLANK(Performer@row), ISBLANK(Start@row), ISBLANK(Finish@row)), "", JOIN(Performer@row + ":" + JOIN(Start@row:Finish@row, "-")))
Your results will look like this:
-
Thank you, everyone! That was perfect!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives