JOIN only if cell is not blank

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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)) '

' <> "" '

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.

EXACTLY what I was looking for. THANK YOU!!!!

LOVE this community.

Andrée Starå

@PeggyLang

Excellent! Glad to hear that it was useful.

I'm always happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PeggyLang

@Paul Newcome
I am looking to do something similar, however, looking for cells that ARE NOT blank in a range. In my instance I would like to collect the names of individuals where there is data in the 'EVEN MTH WK1 TOC' column. I thought I could replace NOT(ISBLANK(@cell)) with a range but I haven't found the correct syntax yet.

Paul Newcome

@PeggyLang

=JOIN(COLLECT(Name:Name, [EVEN MTH WK1 TOC]:[EVEN MTH WK1 TOC], @cell <> ""), "delimiter of choice")

PeggyLang

@Paul Newcome THANKS!!!
I actually let AI generate the formula for me, however, yours is more efficient. I may have asked the question of AI incorrectly. Was my first time using AI.

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.