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!

Find more posts tagged with

Formulas

Join

collect

Accepted answers

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.

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.