Paste multi-paragraph formatted in single cell

Question

I have a table in my word-document with formatted, multi-paragraph/line text (including numbered lists and bullet list). I would like to copy this text in a single cell using a VBA-macro. When I paste the word-cell into an Excel cell, a paragraph of the source is pasted into another row. When I paste it directly into the cell (click into the formula field and paste the content of the clipboard), I loose the formatting. Since an Excel cell does not support HTML-tags, lists etc., it would be alright, if formatted text is converted into plain, replacing the numbered lists into real numbers.

So Question: How can I paste the formatted text as normal structured text into a single cell?

micMike micMike · Accepted Answer · 2017-10-16T13:28:59

I have found a solution approach, which I would like to share:

resultRow=4  ' row number in Excel sheet
' read the number of paragraphs from word table cell (vRow)
i = vRow.Cells(3).Range.Paragraphs.Count
' copy and paste the cell content into Excel
vRow.Cells(3).Range.Copy
ActiveSheet.Cells(resultRow, 3).Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False
' excel has copied the formatted the paragraphs into consecutive rows
' of the selected column
' concatenate the text of the cells
v = ""
For j = 0 To i - 1
   v = v + ActiveSheet.Cells(resultRow + j, 3).Value
   If j < i - 1 Then v = v + Chr(10)

Next j
ActiveSheet.Cells(resultRow, 3).Value = v
' clear all formatting
Selection.ClearFormats
Selection.WrapText = True
Selection.VerticalAlignment = xlTop

Paste multi-paragraph formatted in single cell

1 Answers