How to concatenate a column into one cell? tia sal2

[temp]

Greets all I'm trying to concatenate a column into one cell. Is thier
a way to have concatenate work on a range of cells in a column or a row.

Example: I have Column C2..C45 and I want to have all those
numbers/letters joined in on cell togather on F2.

Tia
SAL2

 

[Pete_UK]

You could do it with a user defined function (UDF) like this:

Function join(my_range As Range) As String
join = ""
For Each my_cell In my_range
join = join & my_cell.Value
Next my_cell
End Function

Use this formula in cell F2:

=join(C2:C45)

Hope this helps.

Pete

 

[Gord Dibben]

The easiest would be to use a User Defined Function or a macro.

Here's a UDF

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(C2:C45) enetered in F2

Returns a comma de-limited list.

Change the cell.text & "," to " " for a space or "" for no space.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown above.


Gord Dibben Excel MVP