=&'', "& or concentrate formula

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

 

[Mike H]

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

 

[Pete_UK]

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

 

[JBeaucaire]

Careful about double posting...

Use the StringConcat() function available here:
http://www.cpearson.com/excel/stringconcatenation.aspx

Steps and instructions to use it were given to you in your other message here
http://www.microsoft.com/communitie...9bf-8fef-e528337714eb&lang=en&cr=US&sloc=&p=1

 

[Gord Dibben]

See your other post for replies.

No need to multi-post.


Gord Dibben MS Excel MVP