Concatenate Text with soft return

**NMullis** · 07-02-2009, 02:33 PM

Good afternoon,
I would like to concatenate text comments from 14 worksheets and place a soft return between each next set of text. To further complicate, not all of the worksheets will have comments. Any thoughts?

Thanks for your help!
N

**tigertiger** · 07-02-2009, 02:37 PM

You should post a demo file?
Comments = Comment of cells?

**NMullis** · 07-02-2009, 02:50 PM

I meant comments as in text information filled in by another user, I apologize for not being clear.

I have posted a basic idea of what I am doing, however concatenating 2 sheets in the example. In place of the " - " I would like to have a return/separate line only if there is text to place there.

Please see attached, hopefully it will explain.

Thank you!

**tigertiger** · 07-04-2009, 02:20 PM

try the following formula:
=SUBSTITUTE(REPT(Sheet2!F6&CHAR(10),LEN(Sheet2!F6)>0)&REPT(Sheet3!F6&CHAR(10),LEN(Sheet3!F6)>0)&" ",CHAR(10)&" ","")

Plz, MENTION about Format result-cells in Wrap text type,
and set autofill for rows' height