Excel: how to concatenate cells with delimiter 4

[kenche0820]

Hi,

I'm looking for an efficient way of concatenating a column of cells, separated by commas. For example, to sum a block of cells I can type =SUM(B4:B9). Is there a comparable function that I can use to make a comma-delimited list?

Thanks for your time.

Kenneth

 

[VBAjedi]

You could do it with a custom function. I just cobbled this one together:

Function ConcatVals(Target As Range)
Dim C As Range
Dim ConcatString As String
For Each C In Target
   If Len(ConcatString) > 0 Then
      ConcatString = ConcatString & ", " & C.Value
   Else
      ConcatString = C.Value
   End If
Next C
ConcatVals = ConcatString
End Function

To use it, put it in a normal code module. Then enter a formula like this into some cell:

=ConcatVals(A6:A12)

Let me know if that does the trick for you!


VBAjedi

 

[kenche0820]

Yup, that worked splendidly. Thanks very much!

 

[kenche0820]

Actually, how would I modify your script to accept multiple parameters? For example, if I want to type =ConcatVals(A6,A8,A10:A13)

 

[VBAjedi]

That gets into non-contiguous ranges, which would make the code more complicated. Better just to call the function several times in your spreadsheet formula, like:

=ConcatVals(A6) & ", " & ConcatVals(A10:A13)



VBAjedi

 

[TheBitDoctor]

kenche0820:

Interesting problem. I thought the VBA JOIN function would work but I couldn't get it to accept a range as an array?!?

Anyway, here's VBAjedi's code with the ability to specify what the delimiter should be and handle multiple non-contiguous ranges. I also removed the IF functions. With the speed of today's computers it doesn't make much difference in performance but the code is a little cleaner.

Regards,
Ron

Function ConcatVals(Delim As String, Target As Range, ParamArray MoreAreas() As Variant)
Dim C As Range
Dim I As Integer
Dim ConcatString As String
For Each C In Target
ConcatString = ConcatString & C.Value & Delim
Next C
For I = LBound(MoreAreas) To UBound(MoreAreas)
For Each C In MoreAreas(I)
ConcatString = ConcatString & C.Value & Delim
Next C
Next I
ConcatVals = Left(ConcatString, Len(ConcatString) - Len(Delim))
End Function

 

[kenche0820]

I just tried running your new function and it accepted non-continguous ranges perfectly. I didn't know how to accept an unknown number of parameters before, so I learned something new as well =)

Thanks very much to both of you for your valuable time and expertise!

Regards,

Ken

 

[Bowers74]

kenche0820:

The way to thank someone at Tek-Tips who has posted a helpful solution is to award them a star. By pressing the Thank MembersName for this valuable post! link at the bottom left of the specific post, you show your appreciation for that member's solution. Awarding stars also allows other members to find helpful posts easier if they are having the same problems.

I will award TheBitDoctor and VBAJedi both stars for you, but feel free to show your appreciation as well (and in the future) for the posts that you find helpful.






Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884