vba 在Excel单元格中的数据之间插入分号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13524881/
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
Inserting semicolon in between data in a cell in Excel
提问by JamesDev
I have data in column A, C, E and G. I want column I to hold all of the data from these separated by semicolons, I have searched the web but all I find is how to replace line breaks with semicolons and that doesn't do it. Below are the 3 pieces of code I have attempted to work into it
我在 A、C、E 和 G 列中有数据。我希望 I 列包含所有数据，这些数据以分号分隔，我在网上搜索过，但我发现的只是如何用分号替换换行符，而这并没有去做吧。以下是我尝试使用的 3 段代码
Range("I" & i).Value = "=A" & i & Chr(59) & "&" & "C" & i & "&" & "E" & i & "&" & "G" & i
Range("I" & i).Value = "=A" & i & "&C" & i & "&E" & i & "&G" & i
Range("I" & i).Value = Range("A" & i).Value + ; + Range("C" & i).Value + ; + Range("E" & i).Value + ; + Range("G" & i).Value
The second line comes closest to what I want but when I add & ";" into it I get errors.
第二行最接近我想要的，但是当我添加 & ";" 进入它我得到错误。
Any ideas thoroughly appreciated
任何想法都非常感谢
SOLVED : This was solved by an answer below, I had managed my own fix which I will detail but the accepted answer is a cleaner, more efficient way
已解决：这已通过下面的答案解决，我已经管理了自己的修复程序，我将详细说明，但接受的答案是一种更清洁，更有效的方法
mystring = "=A" & i & "&" & Chr(34) & ";" & Chr(34) & "& C" & i & "&" & Chr(34) & ";" & Chr(34) & "& E" & i & "&" & Chr(34) & ";" & Chr(34) & "& G" & i Range("I" & i).Value = mystring
mystring = "=A" & i & "&" & Chr(34) & ";" & Chr(34) & "& C" & i & "&" & Chr(34) & ";" & Chr(34) & "& E" & i & "&" & Chr(34) & ";" & Chr(34) & "& G" & i Range("I" & i).Value = mystring
回答by Dante May Code
You should use "&"";""".
你应该使用"&"";""".
In detail, you should use something like
详细地说，你应该使用类似的东西
"=A" & i & "&"";""" & "&C" & i
which result in
这导致
=A1&";"&C1
suppose i = 1.
假设i = 1。
回答by shahkalpesh
=CONCATENATE(A1, ";", C1, ";", E1, ";", G1)
Using VBA
使用 VBA
Range("A2").FORMULA = REPLACE("=CONCATENATE(A1, ';', C1, ';', E1, ';', G1)", "'", CHR(34))
