Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Combining selected cells into one cell ignoring blanks?

Hello all,

I am trying to combine cell into one cell and I have found a couple other community posts covering this issue. The only problem is that their solution only applies if you are trying to collect the entire row, I need specific row.

Originally, I did this just by using JOIN and then selecting each cell, separated by " + ', ' + " which works; however, the empty cells still return with a comma with this formula. The solution I found was: =JOIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, NOT(ISBLANK(@cell))), " - "); however, I do not want the whole row as mentioned before. I tried adjusting this formula several ways and just can't seem to solve the problem.

Any help is appreciated, thanks!

Best Answer

Answers

  • ✭✭✭✭✭✭

    @toca1 Is there a way for you to rearrange the columns so they are contiguous?

  • ✭✭


    Due to the structure of the sheet, it is important that I keep the columns where they are since they are grouped by category: i.e (Animation Request has a dimension column, format column, etc.). However, could I cross reference them on to another sheet with only the columns I wish to combine and apply that same formula there?

  • ✭✭✭✭✭✭
    Answer ✓

    @toca1 Yes, you can do that, or you can create helper columns that grab the text from the cells you want and apply the formula to those instead.

  • ✭✭

    Thank you so much for your quick response and guidance!

  • Community Champion

    Hello @toca1

    Looks like you're biggest issue is with the range references. Set it up similar to this format:

    =JOIN(COLLECT([Helper Column]:[Helper Column], [First Column Name]:[First Column Name],[First Column Name]@row,[Last Column Name]:[Last Column Name],[Last Column Name]@row, [First Column Name]:[First Column Name],NOT(ISBLANK(@cell))), " - ")

    If you specify multiple columns in a single range, you also need to specify which column to return. If you only specify 1 column in a reference, you only have 1 column to return making the function simpler. Select columns individually and you should have less trouble. If you want a combined name, use a helper column like @Eric Law mentioned.

  • ✭✭

    @MichaelTCA

    Thank you! I'll play with this function!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions