合并 2 个 Excel 单元格的内容，保持字符格式不变（使用 VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2192730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge contents of 2 Excel cells keeping character format intact (using VBA)
提问by Doc Brown
As the title says: I try to merge the contents of 2 cells into a 3rd in Excel. There was a similar question here on SO, but those solutions do not keep the character format intact. For example, parts of the source cell contents are formatted bold and red, other parts are normal. When I merge them like
正如标题所说：我尝试将 2 个单元格的内容合并到 Excel 中的第三个单元格中。SO 上有一个类似的问题，但这些解决方案并没有保持字符格式不变。例如，源单元格内容的部分格式为粗体和红色，其他部分是正常的。当我合并它们时
Range("A3") = Range("A1") & Range("A2")
then any formatting of A1 and A2 is lost. What I need is a solution keeping the format intact. This is going to be part of a bigger VBA program, so I need a VBA solution, no formula, please. Excel version is 2002(XP).
那么 A1 和 A2 的任何格式都会丢失。我需要的是一个保持格式完整的解决方案。这将是一个更大的 VBA 程序的一部分，所以我需要一个 VBA 解决方案，没有公式，拜托。Excel 版本为 2002(XP)。
采纳答案by Craig
Doc, that is an interesting question. I was stumped myself but saw the value, so after some searching, here is what I found. From vbaexpressI got the basic understanding of in cell formatting, which I modified for your use below.
博士，这是一个有趣的问题。我被自己难住了，但看到了价值，所以经过一番搜索，这就是我发现的。从vbaexpress我得到了单元格格式的基本理解，我修改了下面供您使用。
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 out the 3 cells() with your specific cells. Maybe someone can find a cleaner way, but when I tested this, it worked as I understand you (and I) would like.
Hope this helps...
只需用您的特定单元格更改 3 个单元格（）。也许有人可以找到一种更简洁的方法，但是当我对此进行测试时，它按照我理解的您（和我）的意愿工作。
希望这可以帮助...
回答by devuxer
This is a bit of a shot in the dark, but if you could somehow write a macro that does the following, it seems like it would work beautifully:
这有点像在黑暗中拍摄，但如果您能以某种方式编写一个执行以下操作的宏，它似乎会很好地工作：
- Copy the entire range (e.g.,
A1:B200) to be merged. - Open a new Word document.
- Paste (creates a table in Word).
- Select the table.
- Do "Convert To Text" with a blank delimiter (or whatever you want).
- Copy the resulting text.
- Paste into desired location (e.g.,
C1) in Excel.
- 复制
A1:B200要合并的整个范围（例如，）。 - 打开一个新的 Word 文档。
- 粘贴（在 Word 中创建表格）。
- 选择表。
- 使用空白分隔符（或任何您想要的）执行“转换为文本”。
- 复制生成的文本。
C1在 Excel 中粘贴到所需位置（例如，）。
I know you can write VBA macros for both Excel and Word, but I have doubts that you could control a Word document from Excel. You probably could write a C# console app that could open and control two documents, however.
我知道您可以为 Excel 和 Word 编写 VBA 宏，但我怀疑您是否可以从 Excel 控制 Word 文档。但是，您可能可以编写一个可以打开和控制两个文档的 C# 控制台应用程序。
回答by Lunatik
I know of no way of doing this directly, but it would not be that hard to copy the ranges to the new range then loop through the interiorproperties of each character of the original ranges and copy the properties over. That should preserve colour, font style etc. for each character in the resulting range.
我知道无法直接执行此操作，但是将范围复制到新范围然后循环遍历interior原始范围的每个字符的属性并复制这些属性并不难。这应该为结果范围内的每个字符保留颜色、字体样式等。
Home time beckons so I've not got time to whip up an example, but I'm sure others edit/answer if need be.
回家的时间在召唤，所以我没有时间举一个例子，但我相信其他人会在需要时编辑/回答。
