Removing blanks from Join Collect output

I have a sheet that is pulling in data for metrics from another sheet. Using Join(Collect() I get the output I want but, it does include the blank cells from the range. I would like to not have them included….

=JOIN(COLLECT({PECR Range 6}, {PECR 2}, Region1), SUBSTITUTE([Column6]1, "-", ""))

Find more posts tagged with

Join

collect

Accepted answers

Matthew 5

@Darren Mullen ,

Thanks, tired this out but it came back as #UNPARSEABLE :( I did go back to my reference sheet just to double check that the 'blank' spaces were actually blank, and they are.

HOWEVER… if you substitute not(blank(@cell)) with not(isblank(@cell)) we are golden :) Thanks for pointing me in the right direction.

All comments

Darren Mullen

@Matthew 5

Try this:

=JOIN(COLLECT({PECR Range 6}, {PECR 2}, Region1, {PECR Range 6}, not(blank(@cell))), SUBSTITUTE([Column6]1, "-", ""))

Matthew 5

@Darren Mullen ,

Thanks, tired this out but it came back as #UNPARSEABLE :( I did go back to my reference sheet just to double check that the 'blank' spaces were actually blank, and they are.

HOWEVER… if you substitute not(blank(@cell)) with not(isblank(@cell)) we are golden :) Thanks for pointing me in the right direction.

Darren Mullen

@Matthew 5 Ugh. Yes, I made that same mistake the other day. I was just typing it from memory.

The correct function is indeed ISBLANK() ! I'm glad you were able to figure it out haha. Sorry for the wrong function name!

Humashankar

Perfect

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.