Help with a Formula to Concatenate Cell Text in a New Line and Skipping Blanks

Hello Everybody,

I have a report which has 10 cells for comments (Text), and they are no contiguous also not all of them will have text.
I want to be able to concatenate them in the way that each comment is one separate line and skip the blank comments.
I found these two formulas that partially works:

1-This formula concatenate the cells contents but do not skip the blanks.

=E.Data!S2&CHAR(10)&E.Data!Y2&CHAR(10)&E.Data!AK2&CHAR(10)&E.Data!AV2&CHAR(10)&E.Data!BB2&CHAR(10)&E.Data!BJ2&CHAR(10)&E.Data!BX2&CHAR(10)&E.Data!CM2&CHAR(10)&E.Data!DB2

2- and this one skip the blanks but do no added in a new line.

=SUBSTITUTE(TRIM(E.Data!S3&" "&E.Data!Y3&" "&E.Data!AK3&" "&E.Data!AV3&" "&E.Data!BB3&" "&E.Data!BJ3&" "&E.Data!BX3&" "&E.Data!CM3&" "&E.Data!DB3)," ",", ")

I've been trying to modify these formulas without sucess. and not be able to find any similar case.

Can someone help me with this one or point me to the right direction?

I really appreciate any help.

Respectfully,

Alex

6StringJazzer,

Thank you for your fast response, I really appreciate it, you are right is only 9 cells, my bad.
When using your formula it does the same as my second formula which skips the blanks but do not add the next cell content in a new line.
I might not explain myself clear, I have attached a sample to illustrate you what I want to get.

In sheet E. Data I have a table which is populated using an user form and have columns with Comments1 and Comments 2, not all the entries will have comments but when the have I want to concatenate them in sheet B, Comments1 will be in column N and Comments 2 will be in column O.

As you can see in N4 I place your formula, which skip the blanks but is only one string.
In N5 is my second formula which does the same.
In O4 is my first formula which add the next comment in a new line but don't skip the blanks and
In O5 is how i want it to be concatenated.

I have no found a macro that could do this, but the formula I'm looking for is to be inserted in a macro, I'm no familiar with the UDF.

Hope you can help me with this problem.

Thanks again for your time

Respectfully,

Alex

6StringJazzer,

Ok, getting close, the formula is going on sheet B which is were you can see my previous explanation, you are right the formula work perfectly, the error was mine because I need to wrap the text in cell.

So now when I put the formula in the following piece of macro it give me the following result:

FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE9-Outcomes:

and this is the macro:

Set WS3 = Worksheets("Weekly Abstraction")
        LastRow5 = WS3.Range("A" & Rows.Count).End(xlUp).Row
        With WS3
          For i2 = 2 To LastRow5
          
          If Len(Trim(.Range("B" & i2).Value)) <> 0 Then
            .Range("N24:N" & LastRow5).Value = "=IF(E.Data!S2="","",E.Data!S2&CHAR(10)) & IF(E.Data!Y2="","",E.Data!Y2&CHAR(10)) & IF(E.Data!AK2="","",E.Data!AK2&CHAR(10)) & IF(E.Data!AV2="","",E.Data!AV2&CHAR(10)) & IF(E.Data!BB2="","",E.Data!BB2&CHAR(10)) & IF(E.Data!BJ2="","",E.Data!BJ2&CHAR(10)) & IF(E.Data!BX2="","",E.Data!BX2&CHAR(10)) & IF(E.Data!CM2="","",E.Data!CM2&CHAR(10)) & E.Data!DB2"
           .Range("N24:N" & LastRow5).Value = .Range("N24:N" & LastRow5).Value
       End If
            
          Next i2
          
        End With

I really don't know what happen

Could you take a look at this, maybe using a macro will be better approach?

Thanks for yor time.

Respectfully,

Alex

6StringJazzer,

Perfect!!!,

You save me a lot of time, I really appreciate your time and patient with me and this work.

Thanks again.

Respectfully,

Alex