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

excelnovice05

Board Regular
Joined
Jan 4, 2005
Messages
66
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jjst34

New Member
Joined
Dec 2, 2003
Messages
13
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,056
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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

Board Regular
Joined
Jan 4, 2005
Messages
66
Hey guys...those recommedations all worked...but jmiskey, yours was the 'golden key' to making my life a whole lot easier.

Thanks again.
 

Forum statistics

Threads
1,147,621
Messages
5,742,188
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top