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

Cabooga

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!!

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!

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