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.

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)
"OnTheEdge" wrote:

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.

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

--
Gary''s Student - gsnu200810


"OnTheEdge" wrote:

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.

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


On Tue, 28 Oct 2008 12:28:10 -0700, Gary''s Student
wrote:

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