Bob

I will provide a macro and a User Defined Function.

Use either as you choose.

To use the macro, just run ConCat_Cells from ToolsMacroMacros

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous",
_
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

To use the UDF, enter in a cell =ConCatRange(A1:A30)

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

Both the Macro and the Function would be copy/pasted to a general module in the
workbook.

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..........

First...create a backup copy of your original workbook.

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo


Gord Dibben MS Excel MVP

On Tue, 13 Jun 2006 21:41:15 -0400, "Bob Newman" wrote:

This is a request for a friend. He has a long list on numbers (usually 10
digits) in a column. Let's say A1:A300. Is there a formula that will enter
all of those numbers into one cell consecutively separated by commas and no
spaces? I guess the question also begs the question of how much information
you can place in one cell. I am unsure of the Excel version although I am
sure it is a late one if not 2003.

Thanks in advance... Bob


Gord Dibben MS Excel MVP