How to convert a column to a string?

M

Marius Waldal

I want a cell to contain a string derived from the values of a certain
column.

This could be column F, row 3, 4 and 5:
Tim
Jenny
Arnie

What I want is a cell showing these values as 1 comma separated string:
Tim,Jenny,Arnie

The number of rows/values must be dynamic! I need to be able to specify
from/to, because there could be hundreds of rows. This will therefore
not work:
F3 & "," & F4 & "," & F5 etc

I pictured a function like this:

Implode( Range; Separator)

to be used like this:

=Implode(F3:F5; ",")

but I can't find anything similar to this. Or any other way...

Any Excel-racers that can give me a hand?

Greatly appreciated

Marius
 
A

akyurek

=MCONCAT(F3:F5;",")

which requires the free morefunc.xll add-in or

=ACONCAT(F3:F5;",")

ACONCAT is a UDF in VBA, due to Harlan Grove. You can find the code via a
Google search.
 
F

Frank Kabel

Hi Marius

you can try the following user defined function
Function Mult_Conc(NameRange As Range, Seperator As String) As String

Dim RetStr As String
Dim c

RetStr = ""
For Each c In NameRange
If c.Value <> "" Then
If RetStr <> "" Then
RetStr = RetStr & Seperator & c.Value
Else
RetStr = RetStr & c.Value
End If
End If
Next c
Mult_Conc = RetStr
End Function

As formula you can enter
=Mult_Conc(A1:A6,",")

Frank
 
Top