Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=&'', "& 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=&'', "& 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=&'', "& 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=&'', "& 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=&'', "& 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concentrate muli cells | Excel Worksheet Functions | |||
How do I write an excel formula with a text answer ("yes" or "no") | Excel Discussion (Misc queries) | |||
Formula Result is "V6", need Excel to use cell "V6", not the resul | Excel Worksheet Functions | |||
any formula to convert numbers in word form, e.g. "2" as "Two"? | Excel Worksheet Functions | |||
conditional formula to show "open" or "closed" | Excel Worksheet Functions |