Google Sheets – Concatenate Non-Blank Specific Cells

concatenategoogle sheetsworksheet-function

I have a formula that breaks when 'nothing' is in any of the cells:

=If(ISBLANK(A1),,A1&IF(ISBLANK(B1),,CHAR(10)&B1&IF(ISBLANK(C1),,CHAR(10)&C1)))

I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE.

Am I going in the right direction with this or is there a simpler way?

Because I am afraid that I will have to do every scenario for every TRUE FALSE in each IF() branch and my real problem is that it has 19 cells that I would like to use if they contain something.

I have a sample sheet here "Concat Cells if not empty".

Best Answer

When you find yourself writing a bunch of similar IF statement, you probably need FILTER. For example, 

=JOIN(CHAR(10), FILTER(A1:F1, NOT(ISBLANK(A1:F1))))

joins the content of cells A1 through F1, separating them with linebreaks — but the empty cells are skipped as they fail the condition NOT(ISBLANK(...)).

Related Topic