=&'', "& or concentrate formula

  • Thread starter Thread starter Dylan @ UAFC
  • Start date Start date
D

Dylan @ UAFC

I have 1000 rows. I need a formula that can
combine all the data in these rows into a single
cell, data seperted by , and then space.
 
Hi,

Right click your sheet tab, view code and paste this in and run it. It
assumes your data are in column A and the output cell is D1

Sub Sonic()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
newstring = newstring & c.Value & ","
Next
newstring = Left(newstring, Len(newstring) - 1)
Range("D1").Value = newstring
End Sub

Mike
 
Note that there is a limit to the number of characters you can have in
a single cell - 32,767 of which only 1024 will display in the cell.
Depending on what you have in those cells, you might easily exceed
these limits. Are you sure you want to do this?

If so, then assuming your data is in column A starting on row 1, put
this in B2:

=A1&", "&A2

and this in B3:

=B2&", "&A3

Then copy this list formula down to cover your data in column A.

Hope this helps.

Pete
 
Back
Top