Merge the contents of two Excel cells while maintaining the character format (using VBA)

Question

As the title says: I'm trying to combine the contents of two cells into a third in Excel. There was a similar question here on SO , but these solutions do not keep the character format intact. For example, parts of the contents of the original cell are formatted in bold and red, other parts are normal. When I merge them like

Range("A3") = Range("A1") & Range("A2") 

then any formatting of A1 and A2 will be lost. What I need is a solution that keeps the format intact. This will be part of a larger VBA program, so I need a VBA solution, without a formula, please. Excel version - 2002 (XP).

Answer 1

Doc, this is an interesting question. I was at a dead end, but I saw value, so after some searching, this is what I found. From vbaexpress, I understood the basic understanding of cell formatting that I changed for your use below.

 Sub Merge_Cells() Dim iOS As Integer Dim rngFrom1 As Range Dim rngFrom2 As Range Dim rngTo As Range Dim lenFrom1 As Integer Dim lenFrom2 As Integer Set rngFrom1 = Cells(1, 1) Set rngFrom2 = Cells(1, 2) Set rngTo = Cells(1, 3) lenFrom1 = rngFrom1.Characters.Count lenFrom2 = rngFrom2.Characters.Count rngTo.Value = rngFrom1.Text & rngFrom2.Text For iOS = 1 To lenFrom1 With rngTo.Characters(iOS, 1).Font .Name = rngFrom1.Characters(iOS, 1).Font.Name .Bold = rngFrom1.Characters(iOS, 1).Font.Bold .Size = rngFrom1.Characters(iOS, 1).Font.Size .ColorIndex = rngFrom1.Characters(iOS, 1).Font.ColorIndex End With Next iOS For iOS = 1 To lenFrom2 With rngTo.Characters(lenFrom1 + iOS, 1).Font .Name = rngFrom2.Characters(iOS, 1).Font.Name .Bold = rngFrom2.Characters(iOS, 1).Font.Bold .Size = rngFrom2.Characters(iOS, 1).Font.Size .ColorIndex = rngFrom2.Characters(iOS, 1).Font.ColorIndex End With Next iOS End Sub 

Just change 3 cells () to specific cells. Maybe someone can find a cleaner way, but when I checked this, it worked as I understand you (and I). Hope this helps ...

Answer 2

This is a little shot in the dark, but if you somehow write a macro that does the following, it looks like it will work beautifully:

• Copy the entire range (e.g. A1:B200 ) to combine.
• Open a new Word document.
• Paste (creates a table in Word).
• Select a table.
• Make "Convert to Text" using an empty separator (or whatever you want).
• Copy the resulting text.
• Paste in the desired location (e.g. C1 ) in Excel.

I know that you can write VBA macros for Excel and Word, but I doubt that you can manage a Word document from Excel. You could probably write a C # console application that could open and manage two documents.

Answer 3

I don’t know how to do this directly, but it would be difficult to copy ranges to a new range, and then iterate over the interior properties of each character of the source ranges and copy the properties. This should preserve color, font style, etc. For each character in the resulting range.

Everyday time beckons, so I don’t have time to pick up an example, but I am sure that others will edit / respond if necessary.