Formula to Join Cells that have Content (with Line Break)

Looking to see if there is a formula that would allow me to join cells into separate lines ONLY for cells that have content. Essentially I want to remove blanks somehow so it is just seamless data.

In my example, when I use CHAR(10). It joins the cells, but leaves blank lines if nothing was filled in for a cell.

Im currently using =JOIN(A4:A61, CHAR(10))

Thanks in advance!!

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

Hi @Cabooga,

Use a JOIN COLLECT instead:

=JOIN(COLLECT(A4:A61, A4:A61, <>""), CHAR(10))

This should eliminate the blank spaces

Sample (using slightly less rows!):

Hope this helps, but if you've any problems/questions then let us know!

All comments

Nick Korna

Hi @Cabooga,

Use a JOIN COLLECT instead:

=JOIN(COLLECT(A4:A61, A4:A61, <>""), CHAR(10))

This should eliminate the blank spaces

Sample (using slightly less rows!):

Hope this helps, but if you've any problems/questions then let us know!

Cabooga

Oh my word, you are magic! That worked perfectly. Thank you! 🎉

bisaacs

Hi @Cabooga,

I think if you add COLLECT to your formula, it should work:

=JOIN(COLLECT(A4:A61, A4:A61, ISTEXT(@cell)))

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.