How do I join multiple rows of texts into one cell based on another column's criteria?

Faith

Above is a screenshot of the sheet I'm currently working with.

In each cell of the third column, "List of Tasks", I want to enter a formula that will join all of the text in each row of the "Tasks" column but only include text whose row 2, "Task Completed", states "No". I know how how to use the join function, but not if I'm incorporating a certain criteria. Would I have to use a "collect" formula inside of the join formula? What would this look like?

Also, when I do join the text, I want the delimiter to be an "enter" value instead of just a typical space, dash, or comma. Is this possible? I think in Excel you can use "Ctrl + J" to designate "Enter" but I can't seem to get it to work here.

Paul Newcome

It would look something like this:

=JOIN(COLLECT(Tasks:Tasks, [Task Completed]:[Task Completed], @cell = "No"), CHAR(10))

Paul Newcome

It would look something like this:

=JOIN(COLLECT(Tasks:Tasks, [Task Completed]:[Task Completed], @cell = "No"), CHAR(10))

Netty99

https://community.smartsheet.com/discussion/comment/415833#Comment_415833

Is "CHAR(10) a default? What does the "10" represent? I saw it elsewhere as well which is why I ask. Thank you.

Andrée Starå

Hi @Netty99

I hope you're well and safe!

The 10 is the code for a new line; here are more codes you can use.

https://www.freecodecamp.org/news/ascii-table-hex-to-ascii-value-character-code-chart-2/

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Netty99 CHAR(10) is the "new line" delimiter.