JOIN only if cell is not blank

BarneeL

Hi there,

 

Is there a way I can use the JOIN function to merge the contents of an entire row from another sheet, but exclude rows that are blank?

The contents of the other sheet is generated from a form (and being constantly updated), so referencing the entire column is the only option unfortunately.

It seems like it should be easy, but I am drawing a blank on how to do it.

Andrée Starå

Hi Barnee,

Yes, but you will get the delimiters between or everything will be without spaces.

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

If you are looking to join everything for the row on the sheet itself, you can use

 

=JOIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, NOT(ISBLANK(@cell))), " - ")

 

You can then use cross sheet references to pull that column to the new sheet. If you are able to provide more details as to what you are doing exactly and how you want things to work and be displayed along with some screenshots, we may be able to help figure out a more useful solution for you.

 

Edit: Andree and I must have been typing at the same time. With the above formula, you can specify delimiters where you see the " - ". With the COLLECT function, you wouldn't have a bunch of useless delimiters shown in place of all of the blank cells, only in between whatever data is pulled from the populated cells.

Andrée Starå

 Nice solution Paul!

I didn't think about using COLLECT.

Have a fantastic weekend!

Best,

Andrée

BarneeL

Thank you both! 

Thanks Paul, I also didn't think of using the COLLECT function. I used your formula, except changed

' NOT(ISBLANK(@cell)) '

to

' <> "" '

Works great. Thank you!

Andrée Starå

I'm always happy to help!

Have a fantastic weekend!

Best,

Andrée

Paul Newcome

Thanks. I have a handful of sheets where I have people filling out forms and making selections. To make it easier on them I have it set as checkboxes. I then have a text type column where I use a JOIN(COLLECT( to pull the text value for any columns where they have checked the box to display their entire selection.

 

I then use place holders and an auto-number system to send an Alert to the email address they entered on the form to give them a sort of ticket number and summary of their form entry. I've posted (a while back) on here a few times about this setup, and I love it.

 

Needless to say, I use JOIN(COLLECT( quite frequently now as it can make things so much easier and cleaner when displayed on a sheet/dashboard.

Paul Newcome

Happy to help. I keep forgetting about <> in place of the NOT function. takes a few less key strokes, so I think I'm going to have to start using it.