CODEWITHSUNDEEP
vbaexcel
David Klempfner
David Klempfner

Reputation: 9868

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"

Upvotes: 14

Views: 28271

Answers (5)

user2140173
user2140173

Reputation:

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 a UDF

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

Upvotes: 22

T.M.
T.M.

Reputation: 9948

Using the new dynamic worksheetfunction TextJoin() of Microsoft 365/Excel2019 (+/-Mac) and Excel for the Web you can build a udf with the following range arguments

  • (1) a column or
  • (2) a row or even
  • (3) a contiguous range input (e.g. "A2:C5")

The optional 2nd argument ExcludeBlanks allows to omit blank values.

The function result is a comma separated list (important for case (3): the reading order is row wise).

Function Rng2List(rng As Range, Optional ExcludeBlanks As Boolean = True) As String
    Rng2List = WorksheetFunction.TextJoin(",", ExcludeBlanks, rng)
End Function

See help at Textjoin function

Upvotes: 0

Matthew Davison
Matthew Davison

Reputation: 1

Public Function COLSASLIST(Rng As Range) As String
    Dim tempStr1 As String
    tempStr1 = Replace(Replace(Join(Application.Transpose(Application.Transpose(Rng.Value)), ","), ",,", ""), ",,", ",")
    If Right(tempStr1, 1) = "," Then tempStr1 = Left(tempStr1, Len(tempStr1) - 1)
    COLSASLIST = tempStr1
End Function

Public Function ROWSASLIST(Rng As Range) As String
    Dim tempStr1 As String
    tempStr1 = Replace(Replace(Join(Application.Transpose(Rng.Value), ","), ",,", ","), ",,", ",")
    If Right(tempStr1, 1) = "," Then tempStr1 = Left(tempStr1, Len(tempStr1) - 1)
    ROWSASLIST = tempStr1
End Function

Upvotes: 0

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

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.

Upvotes: 2

Kip Bryan
Kip Bryan

Reputation: 461

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)),",")

Upvotes: 0

Related Questions