How to put Column of numbers into one cell?

[Cordo]

I get a daily list of numbers that I need to put into a data base. It arrives in an excel sheet and can be up to 40,000 total. I need to find a way (if possible) to put the numbers in this way eg 001, 002, 003 etc

So I tried concatenating the numbers "001" with ", "

That worked fine but I need to get them into one continous line rather than in a column. I have tried formulas and exporting them into notepad but it doesn't seem to work. Hope I have been clear enough with my explanation.

Again my numbers come like this.

001
002
003
004


I change them to this
001,
002,
003,
004,


But I really need this
001, 002, 003, 004 (In one continuous line).

Hope you can help.
Cheers,
Cordo.

 

[Andrew Poulsom]

This worked for me:

=A1&","&A2&","&A3&","&A4

 

[Peter_SSs]

... can be up to 40,000 total. I need to find a way (if possible) to put the numbers in this way eg 001, 002, 003 etc

Not all 40,000 in one line I hope?

 

[P.Holko]

Check if this helps:
http://www.mrexcel.com/forum/showthread.php?t=575582

 

[P.Holko]

The maximum amount of cell is 32 767 characters so the VBA procedure may be the only solution (particularly with 40 000 cells' value).

Code placed below is one of the possible solutions.
Note that you must have "Microsoft Forms 2.0 Object Library" VBA reference selected to conduct this procedure!

Sub Combine()

'IMPORTANT! to use this procedure (to use the DataObject in your code), you must set _
a reference to the Microsoft Forms 2.0 Object Library (to do this: Visual Bacic -> Tools menu -> References... -> _
select 'Microsoft Forms 2.0 Object Library')

Dim Arr() As Variant
Dim total As String
Dim DataObj As New MSForms.DataObject
Set RangeN = Application.InputBox(Prompt:="Select range with your numbers", _
    Title:="!!", Type:=8)

If (RangeN Is Nothing) Then
    MsgBox "Error, no data."
    Exit Sub
End If

Arr = RangeN.Value

total = vbNullString
For i = LBound(Arr, 1) To UBound(Arr, 1)
    For j = LBound(Arr, 2) To UBound(Arr, 2)
        If i = 1 And j = 1 Then
            total = Arr(i, j)
        Else
            total = total & ", " & Arr(i, j)
        End If
    Next j
Next i

DataObj.SetText total
DataObj.PutInClipboard
PutInClipboard = True

End Sub

After running above procedure you can paste your newly-created database in .TXT or .DOC file.