将范围转换为逗号分隔的字符串

If I had a column like this:

如果我有这样的列：

Col1
abc
def
ghi
jkl

How can I convert it to a string like this?:

如何将其转换为这样的字符串？：

"abc,def,ghi,jkl"

2 个解决方案

#1

You can use the Join() function to join all the elements of a 1 dimensional array with a delimiter.

您可以使用Join（）函数将1维数组的所有元素与分隔符连接起来。

The Transpose() function is used below to form the dimensional array (this approach works on a single column or row).

下面使用Transpose（）函数来形成维数组（此方法适用于单个列或行）。

Sub Main()
    Dim arr
    arr = Join(Application.Transpose(Range("A2:A5").Value), ",")
    MsgBox arr
End Sub

or as an UDF

或作为UDF

Public Function Merge(r As Range) As String
    Merge = Join(Application.Transpose(r.Value), ",")
End Function

#2

Double-transpose works for doing string join on single-row values. Thanks @user2140173 and @brettdj!

双转置适用于对单行值进行字符串连接。谢谢@ user2140173和@brettdj！

debug.print join(Application.Transpose(Application.Transpose(Range("A1:G1").Value)),",")

debug.print join（Application.Transpose（Application.Transpose（Range（“A1：G1”）。Value）），“，”）