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?



  • KPH
    KPH ✭✭✭✭✭✭

    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.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    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!