How would I merge contents from 457 cells into one cell?

[excelnovice05]

I am looking to take all of the contents from column "A", from A2:A458 and merge them into cell B1. At the same time I am trying to seperate the values by a comma. Therefore right now they are independent values for example A1 = 34234 A2 = 34958 A3 = 97583...A458 = 32424. I want to get all of those numbers into cell B1 = 34234, 34958, 97583...32424. Please let me know if you have any insights.

 

[jjst34]

a simple way would be
=A2&","&A3&","&A4&","&A5

etc...

there is probably an less tedious way in vba, however, I am not that great to come up w/ it in a minute,, but i will try later, though i'm sure someone will get it quicker than me..

 

[Joe4]

Here is a quick little macro that will do that.

If you need to do this on multiple cells, we can easily turn this macro into a User Defined Function.

Sub PopulateCell()

    Dim i As Long
    Dim myString As String
    
    For i = 2 To 458
        myString = myString & Range("A" & i) & ", "
    Next i
    
    myString = Left(myString, Len(myString) - 2)
    Range("B1") = myString
    
End Sub

 

[GorD]

You may have a problem displaying all the contents depending on the length of text in col A. Excel 2000 only dispays 1024 characters in the cell although more are dispayed in the formula bar when cell is selected.

 

[excelnovice05]

Hey guys...those recommedations all worked...but jmiskey, yours was the 'golden key' to making my life a whole lot easier.

Thanks again.