Convert range to comma delimited string

问题:

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"

回答1:

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

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

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

or as an UDF

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

回答2:

Just in case you need heavier machinery use one of the solutions provided in the answer below. I had similar challenge for ranges containing milion of cells. In such cases JOIN will lead to crash.

Check the question here: Turn Excel range into VBA string

I have tested all the approaches provided in the above link. Solutions based on function JOIN have slow performance, or even lead to crash.

Ordinary loop through all the cells is way faster than JOIN function. The sting builder in accepted answer is even faster. With string builder, the strings consisting of millions of cells are build in seconds. This is the solution I have end up with.

回答3:

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

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