=&'', "& or concentrate formula
 

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.

=&'', "& or concentrate formula
 

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

"Dylan @ UAFC" wrote:

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.

=&'', "& or concentrate formula
 

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

On Jan 13, 6:49*pm, Dylan @ UAFC
wrote:
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.

=&'', "& or concentrate formula
 

Careful about double posting...

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

Steps and instructions to use it were given to you in your other message he
http://www.microsoft.com/communities...r=US&sloc=&p=1

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Dylan @ UAFC" wrote:

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.

=&'', "& or concentrate formula
 

See your other post for replies.

No need to multi-post.


Gord Dibben MS Excel MVP

On Tue, 13 Jan 2009 10:49:01 -0800, Dylan @ UAFC
wrote:

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.