Combining selected cells into one cell ignoring blanks?

toca1

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!

Eric Law

@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.

Eric Law

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

toca1

https://community.smartsheet.com/discussion/comment/397974#Comment_397974

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?

Eric Law

@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.

toca1

https://community.smartsheet.com/discussion/comment/397979#Comment_397979

Thank you so much for your quick response and guidance!

MichaelTCA

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.

toca1

@MichaelTCA

Thank you! I'll play with this function!