Combining Cells

[ian_gendreau]

I need the easiest way to do this:

I have a column with values. Sometimes there are 2 rows, sometimes 20.
For another program, I need all the values in that column in one long
string separated by commas. So if my orginal data is:

8766
7788
9987

What I need excel to spit out is: 8766,7788,9987

How do I do this given that my number of rows can vary. I don't mind
at all copying and pasting a function in that handles this, but just
not sure how to go about it. Any help is appreciated. Thanks!

 

[Bernard Liengme]

Let's say the numbers start in A1
In B1 enter = A1
In B2 enter =B1&","&A2 (or =B1&", "&A2 if you need a spaces after the
comma)
Copy this down the column to the last value in A; the simplest way is to
double click the fill handle which is the small,solid square in the lower
right corner of the active cell.
best wishes

 

[Gord Dibben]

Ian

This UDF can do the job. Copy it to a general module in your workbook.

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

Assuming values in A1:A12 in A13 enter =ConCatRange(A1:A12)


Gord Dibben MS Excel MVP