Combining text from multiple cells into a single cell and coping this to other cells

[Intern5151]

I am looking for help on combining text from multiple cells into one cell and copy the formula.

Additionally, I need the texts that are represented within the different cells to be on different lines within that individual cell.

Also it is important that the text lines up on the relevant row as vital information is stored in adjacent columns.

Adding to the complexity is that there are number of empty cells following each item. (Deleting these cells cause mismatch between goods and their specs/text)

Example of what I'm currently doing using the following formula (This is very time consuming and there are thousands/tens of thousands items to do this with)

=CONCATENATE(TRANSPOSE(E2:E5 & " " & CHAR(10)))

Followed by highlighting transpose and it succeeding function and then using F9

Which then produces this

=CONCATENATE("{Line of text
","Line of text
","Line of text
","Line of text
"})

Which I then delete both {} and hit enter leaving me with

=CONCATENATE("Line of text
","Line of text
","Line of text
","Line of text
")

Thanks in advance

[XLent]

Given your version, 2016, you could perhaps use something along the lines of below:

Please Login or Register  to view this content.

cells should be format to Wrap, with Vertical Top etc...