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

#### excelnovice05

##### Board Regular
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### jjst34

##### New Member
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.

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
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
Hey guys...those recommedations all worked...but jmiskey, yours was the 'golden key' to making my life a whole lot easier.

Thanks again.

Replies
0
Views
153
Replies
8
Views
117
Replies
1
Views
266
Replies
2
Views
601
Replies
7
Views
147

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,486
Messages
5,770,373
Members
425,612
Latest member
martinijr

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

### Which adblocker are you using?

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

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