Team,

I have a requirement in Excel do concatenate multiple rows of column D (D2 to D100) one below the other and show it in a single cell E2. So for that I have applied the formula in E2 as below.

Formula

=D2&"
"&D3&"
"&D4&"
....
....

"&D99&"
"&D100&""

But my requirement is not fixed i.e (1 to 100). it is conditionally based if we have upto 60 rows, we have to write the above formula upto D60.

Is there anyway we can write a function to concatenate mutiple rows (D2 to D1000) upto a fixed number 1000 and if the text do not appear in the Cells we should not show it in Cell E2 conditionally.

Thanks!
Kiran