2

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".

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
2
  • OK I can see how it works with a range how about individual cells. for example: I only need the following cells A1, F1, L1, V1 and to separate them with linebreaks.
    – CamSyl
    Commented May 1, 2015 at 2:38
  • 1
    If you have to put together several cells scattered around a spreadsheet, I suggest adding another row/column with =A1 =F1 =L1 =V1 in it, and then joining that row as above.
    – user79865
    Commented May 1, 2015 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.