vba 如何为列中的所有值添加单引号和逗号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42915255/
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 add single quote and comma to all values in a column?
提问by me24hour
I have a column hostwith following values:
我有一列host具有以下值：
server1.abc.com
server2.abc.com
server3
Now, I want each of these values to be single quotes and separated by comma. The output should be like as below:
现在，我希望这些值中的每一个都是单引号并用逗号分隔。输出应如下所示：
'server1.abc.com','server2.abc.com','server3'
Please let me know how this can be achieved using Excel.
请让我知道如何使用 Excel 实现这一点。
So far I am able to separate values using ,with following code:
到目前为止，我能够使用,以下代码分隔值：
Public Function MergeME(r As Range) As String
MergeME = Join(Application.Transpose(r.Value), ",")
End Function
How can I add double quotes around values in each cell??
如何在每个单元格中的值周围添加双引号？
回答by CallumDA
Your original UDF was close. You just need to join with ','rather than ,and then place 'at the start and end of your output string
您的原始 UDF 很接近。您只需要加入','而不是,然后放置'在输出字符串的开头和结尾
Public Function MergeME(r As Range) As String
MergeME = "'" & Join(Application.Transpose(r.Value), "','") & "'"
End Function
Output string as required:
根据需要输出字符串：
'server1.abc.com','server2.abc.com','server3'
回答by Gary's Student
With data in A1through A3, in B1enter:
使用A1到A3 中的数据，在B1 中输入：
="'"
(this is a single quote encased in double quotes)
（这是用双引号括起来的单引号）
Then in B2:
然后在B2 中：
=B1 & A1 & B1 & "," & B1 & A2 & B1 & "," & B1 & A3 & B1
回答by Web B
NO functions or VBA are needed
不需要函数或 VBA
In cell B1 input this = CHAR(39)
In cell C1 input this ,
In cell D1 = CONCAT($B,A1,$B,$C)
In cell D2 = CONCAT(D1,$B,A2,$B,$C)
from D2 - D(x) this is automated you can copy Cell D2 to Cell D3 yielding this:
从 D2 - D(x) 这是自动化的，您可以将单元格 D2 复制到单元格 D3，从而得到：
in cell D3 = CONCAT(D2,$B,A3,$B,$C)
YOU COULD FIND THE LARGEST LENGTH in the Dx column via lookup and then get your final output string; i didn't do that here-- i added the following:
您可以通过查找在 Dx 列中找到最大长度，然后获得最终输出字符串；我没有在这里做——我添加了以下内容：
In cell E3 = LEN(D3)
i take the final comma out:
我去掉最后一个逗号：
In cell F2 = LEFT(D3,E3-1)
very automated task
非常自动化的任务
