convert column to a cell with formatting

[OnTheEdge]

I am trying to figure out the best way to convert the following:
1
2
3

To (in a single cell):
1,2,3

I'm guessing a function? The list is variable, meaning it could have any
number of rows up to about 50 or so.

 

[Gary''s Student]

Try the following UDF:

Function mergum(r As Range) As String
mergum = ""
oneshot = 1
For Each cell In r
If oneshot = 1 Then
mergum = cell.Value
oneshot = 0
Else
mergum = mergum & "," & cell.Value
End If
Next
End Function

 

[Sheeloo]

If you can live with a helper column then enter this in B1
=A1
and this in B2 and copy down
=B1&IF(A2="","",","&A2)

Last cell will contain what you want
You can have a cell at top =B1000 or soemthing to get it at top (1000 being
much more that your last col and copy formula down to 1000)

 

[Gord Dibben]

Or this one which ignores blank cells if any are present in the range.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(range)


Gord Dibben MS Excel MVP