Blank do not Concatenate

Options

Hello,

I have a cell i use to concatenate a varying cells into one line and divides each text by dashes.

Currently i'm using

=column1@row + "-" column2@ow + "-" +column2@row

Is there a way to spin the isblank( or if(blank formula so that if all columns 1 2 or 3 cells are blank it would not use the formula so that the dashes do not show up?

Tags:

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    @Enoch Mak, here are two approaches..

    Use COLLECT() and wrap it in JOIN().

    COLLECT(column1@row:columnX@row, column1@row:columnX@row, <>"")

    =JOIN(COLLECT(column1@row:columnXX@row, column1@row:columnXX@row, <> ""), "-")

    The other approach is to use IF().

    =column1@row + IF(column2@row = "", "", "-" + column2@row) + IF(column3@row = "", "", "-" + column3@row) + IF(column4@row = "", "", "-" + column4@row)
    
  • Enoch Mak
    Options

    Thank you @Toufong Vang !

    I think this is the step in the right direction for me with the Join / Collect formula.

    I was wondering For some of the join/collects I have break up the columns ie Title should be like so...

    Column 1 - Column 2 - Column 4 - Column 5

    The IF formula seems to work well but requires me to write out much more would there be a way to still use the Join/Collect formula while maintaining the order words within title that i want?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    Column 1 - Column 2 - Column 4 - Column 5

    In that case JOIN(COLLECT()) will not work, because COLLECT() requires a range. If you have something in Column 3, JOIN(COLLECT()) will include that value.

    Documentation of Smartsheet functions: https://help.smartsheet.com/functions

  • Enoch Mak
    Options

    @Toufong Vang understood, i'll see if there's a work around this by reordering the columns, but if not i'll stick with the manually selecting the specific order i need + IF function to NOT display to the blank cells. I appreciate the suggestions!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!