Removing blanks from Join Collect output

Matthew 5

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, "-", ""))

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

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