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

@toca1Yes, 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

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

toca1

@toca1<\/a> Is there a way for you to rearrange the columns so they are contiguous?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"toca1\",\"userID\":167760}}},{\"insert\":\" Is there a way for you to rearrange the columns so they are contiguous? \\n\\n\"}]","format":"rich","dateInserted":"2023-10-02T20:42:58+00:00","insertUser":{"userID":149069,"name":"Eric Law","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20Law","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/LJFYWOPGFF2A\/n0RM7TH9F54SP.png","dateLastActive":"2023-10-03T20:29:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397974#Comment_397974","embedType":"quote"}"> 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

@toca1Yes, 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

@toca1<\/a> 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.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"toca1\",\"userID\":167760}}},{\"insert\":\" 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.\\n\"}]","format":"rich","dateInserted":"2023-10-02T20:52:20+00:00","insertUser":{"userID":149069,"name":"Eric Law","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20Law","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/LJFYWOPGFF2A\/n0RM7TH9F54SP.png","dateLastActive":"2023-10-03T20:29:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397979#Comment_397979","embedType":"quote"}"> 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 Lawmentioned.

toca1

@MichaelTCA

Thank you! I'll play with this function!