How to use JOIN and not return "0" when all cells are blank.

Ami Veltrie

I've seen a few strings on this same topic but I'm still unable to get it to work.

Here's my formula:

=JOIN(COLLECT([Gas Vendor Name]@row + [Sewer Vendor Name]@row + [Water Vendor Name]@row, NOT(ISBLANK(@cell))))

Ami Veltrie

@Andrée Starå

Please disregard. I found the answer. Thank you for your help!

=JOIN([Gas Vendor Name]@row + JOIN([Sewer Vendor Name]@row + JOIN([Water Vendor Name]@row)))

Bassam Khalil

@Ami Veltrie

Hope you are fine, i don't think this is the correct syntax for Join with Collect , Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Ami Veltrie

I hope you're well and safe!

Try something like this. (change the column names to match yours)

Are the columns together or are they spread out?

=JOIN(COLLECT([Column 1]@row:[Column 2]@row, [Column 1]@row:[Column 2]@row, <>""), 
CHAR(10))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

Ami Veltrie

@Andrée Starå Good morning and thank you!

My columns are spread out. ie. other columns in-between them. Does that change the formula that you provided?

Ami Veltrie

@Bassam Khalil

Ami Veltrie

@Andrée Starå Hi!

Here's where I'm at with your suggested formula. Still not working.

=JOIN(COLLECT([Gas Vendor Name]@row, [Sewer Vendor Name]@row, [Water Vendor Name]@row, <>""))

Keep in mind I'm not looking for the cells to concatenate. I'm only trying to return one of the three cell's value (as the other two will be blank).

**Here's my original formula and screen cap. It keeps putting a "0" in front of the result:

Ami Veltrie

@Andrée Starå

Please disregard. I found the answer. Thank you for your help!

=JOIN([Gas Vendor Name]@row + JOIN([Sewer Vendor Name]@row + JOIN([Water Vendor Name]@row)))

Andrée Starå

@Ami Veltrie

Excellent!

You're more than welcome!

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