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


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!