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

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

  • 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 May 1 '15 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 May 1 '15 at 2:46
  • That Worked, Thanks so much! – CamSyl May 1 '15 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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