Copying many cells, drop content into one cell? THANKS!

I have a document I was directed to fill in. I was instructed to only use a certain number of characters and then drop into another cell below it for paragraphs of text.

I have now been told they made a mistake, need me to copy all the related cells for a specific paragraph and drop them into a single cell.

Can I copy the text in multiple cells, and then drop the cell formating of those copied cells and drop the content into one single cell. Either word wrapping the content or soft returns for what looks like paragraphs again?


Thanks for your help in advance!

You cannot use formulas to apply multiple formats to various text in a cell, but you can collect text strings from various sources and concatenate them into a single cell, including line feeds.

But this stuff is fraught with problems, so rather than just guess at your need, mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

String concatenation can be a bit of a frustrating process, but it's doable. You can keep adding "needs" to this to make it unmanageable, but the ability to point to a range of cells and say "concatenate all these text strings into a single cell"...that's not that hard to ADD INTO your spreadsheet.

I have several User Defined Functions (UDFs) that can be added to your sheet to give you the new function. The simplest I use is called ConCatRange() and it doesn't take any parameters other than the range of cells you want to join.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range, sbuf As String
    
    For Each Cell In CellBlock
        If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & " "    'Make this ", " to string together with comma delimiters
    Next Cell
    
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

How to install the UDF:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The UDF is installed and ready to use.

It's used in your sheet, for example, like this:

=ConCatRange(G1:G6)