vba 如何连接数百个单元格中的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17530586/
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
How to concatenate strings from hundreds of cells?
提问by user1681664
I am trying to combine sentences from various cells into one cell. Example:
我试图将来自不同单元格的句子合并到一个单元格中。例子：
A1 - "hello"
A2 - "how are"
A3 - "you"
A1 - “你好”
A2
- “你好吗” A3 - “你”
A4 - =combine(A1:A3) ---- > "hello how are you"
A4 - =combine(A1:A3) ---- > “你好，你好吗”
I know this trick: =A1 & " " & A2.....but I have 700 cells to combine into a single cell and this approach seems barbaric. If there is a built in function, that would be ideal. I don't mind an answer in VBA as long as the answer is extremely detailed starting with how to open VBA, as I do not know VBA.
我知道这个技巧：=A1 & " " & A2.....但我有 700 个单元格组合成一个单元格，这种方法看起来很野蛮。如果有内置函数，那将是理想的。我不介意 VBA 中的答案，只要答案从如何打开 VBA 开始非常详细，因为我不知道 VBA。
回答by ssarabando
There is but it isn't much better: concatenate.
有，但也好不到哪里去：concatenate。
In your case, that would be concatenate(A1;" ";A2;" ";A3).
在您的情况下，那将是concatenate(A1;" ";A2;" ";A3).
Neither are good ways to deal with 700 cells.
两者都不是处理 700 个细胞的好方法。
A VBA solution would be better. To open the VBA editor, press ALT+F11 (for a graphical explanation, see this article).
VBA 解决方案会更好。要打开 VBA 编辑器，请按 ALT+F11（有关图形说明，请参阅此文章）。
Afterwards, go to the "Insert" menu on top and select "Module". The editor will then be ready to accept input. Just paste the following (delete any other text that it may have):
然后，转到顶部的“插入”菜单并选择“模块”。然后编辑器将准备好接受输入。只需粘贴以下内容（删除它可能包含的任何其他文本）：
Option Explicit
Public Function MyConcatenate(ByVal myRange As Range)
Dim vCell, vResult As String
For Each vCell In myRange.Cells
vResult = vResult & " " & vCell.Text
Next
MyConcatenate = Mid(vResult, 2)
End Function
You can now close the editor and return to the workbook.
您现在可以关闭编辑器并返回到工作簿。
To use it, write the following in a cell: =MyConcatenate(A1:A3).
要使用它，请在单元格中写入以下内容：=MyConcatenate(A1:A3).
回答by Rubens Mariuzzo
You can use the StringConcatfunction found in this page: http://www.cpearson.com/excel/stringconcatenation.aspx
您可以使用StringConcat此页面中的函数：http: //www.cpearson.com/excel/stringconcatenation.aspx
Then you can use it like this:
然后你可以像这样使用它：
=StringConcat("|",B1:B5)
An you will received all values from the range: B1:B5separated by |.
您将收到范围内的所有值：B1:B5由|.
In case the link is broken, you can find the source of the function here: http://pastebin.com/JNS9pYWA.
如果链接断开，您可以在此处找到该函数的来源：http: //pastebin.com/JNS9pYWA。
回答by pnuts
For a relatively modest 700 cells formulae would seem viable.
对于相对适中的 700 个单元格，公式似乎可行。
in B1: =A1&" "
in C1: =B1
in B2: copy B1(ie =A2&" ")
in C2: =C1&B2
在 B1 中：=A1&" "
在 C1 中：=B1
在 B2 中：
在 C2 中复制B1（即=A2&" "）
：=C1&B2
then copy down B2:C2as required.
然后B2:C2根据需要复制下来。
回答by Kash
I too have the same problem. I have data in column A from A1:A980.
But I have found out the solution as below.
我也有同样的问题。我在 A 列中有数据来自A1:A980. 但我已经找到了如下解决方案。
In B2 I put the formula as =CONCATENATE(B1," ",A1," ",A2)and from B3 I entered formula as =CONCATENATE(B2," ",A3)and in B3 as =CONCATENATE(B3," ",A4)till B980.
在 B2 中，我将公式作为=CONCATENATE(B1," ",A1," ",A2)和从 B3 输入公式作为=CONCATENATE(B2," ",A3)和在 B3 中=CONCATENATE(B3," ",A4)直到 B980。
This will give you your result in last B90 cell and that too without any VBA.
这将为您提供最后一个 B90 单元格的结果，并且无需任何 VBA。
Hope you might have the same problem then this might solve the issue.
希望你可能有同样的问题，那么这可能会解决问题。
