How to convert a column to a string?

  • Thread starter Thread starter Marius Waldal
  • Start date Start date
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
 
=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.
 
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
 
Marius,
If you will always have names in all three cells then you can simply use:

=F3&","&F4&","&F5

If sometimes the range will have empty cell then use this formula modified from a previous posting by Harlan Grove.

=SUBSTITUTE(TRIM(F3&" "&F4&" "&F5)," ",",")

Neither of these requires Add-ins or Visual Basic.

Good Luck,
Mark Graesser
(e-mail address removed)


----- Marius Waldal wrote: -----

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
 
Back
Top