Can a function do this?
 

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

Can a function do this?
 

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

Can a function do this?
 

A cell can contain about 32k characters--but only about 1000 characters will be
visible in the cell (unless you add some alt-enters every 80-100 characters.

One way to put all those cells into one cell (but all the characters won't be
viewable):

Put this in B1:
=a1

Put this in B2:
=b1&","&A2
and drag down the column.

select that last cell and
edit|copy
edit|Paste special|values

And you can clear the cells B1:b299.

==========
If you want to see all the numbers in that last cell, you could add the
alt-enters (char(10)'s) every so often.

In B1:
=A1
(no change)

In B2:
=B1&","&IF(MOD(ROW(),7)=0,CHAR(10),"")&A2
And drag down

Convert to values and clean up those other cells.

And format that cell nicely.
Widen the column
format|cells|alignment tab|check Wrap Text
Deepen the row height if you have to.



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

--

Dave Peterson