Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
Is there away to create a macro to put numbers in one row with comma
between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
the last column is IV. there is a limit of 230 columns. the number of rows
can be in the 1000. You will not get all your data in 1 row. "MarkyMark" wrote: Is there away to create a macro to put numbers in one row with comma between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
IV is column 256 and xl2007 extended that to 16384 columns (XFD).
And there's 64k of rows in xl97-xl2003. 1Meg in xl2007. But I agree with your question.... 10 characters surrounded by a pair of double quotes and separated by a comma would mean that: 3000 * (10+2+1) Characters in that one giant cell. 3000 * 13 39000 And excel can hold about 32k characters per cell. Maybe the question becomes: What does the OP really want? Joel wrote: the last column is IV. there is a limit of 230 columns. the number of rows can be in the 1000. You will not get all your data in 1 row. "MarkyMark" wrote: Is there away to create a macro to put numbers in one row with comma between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
10 * 3000 = 30000 + 2999 commas + 6000 double quotes would make more
characters than a cell can hold. Sub CCC() Dim cell As Range, s As String For Each cell In Range("A1:A3000") s = s & """" & cell.Value & """," Next Range("B9").Value = Left(s, Len(s) - 1) End Sub would be the basic approach -- Regards, Tom Ogilvy "MarkyMark" wrote: Is there away to create a macro to put numbers in one row with comma between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
As Dave pointed out, getting 10 lbs into a 5 lb bag is difficult. If you can live with multiple bags then (data is in B5:B9)... Sub InsertComma() Dim vArr As Variant vArr = Join(Application.Transpose(Range("B5:B9")), ",") Range("C5").NumberFormat = "@" Range("C5").Value = vArr End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "MarkyMark" wrote in message Is there away to create a macro to put numbers in one row with comma between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
Yes, but with 3000 10-digit numbers including commas, you would exceed Excel's
32.767 character limit per cell. You also would not be able to see or print more than about 100 of them due to Excel's 1024 limit on visible characters in a cell. Still want to do this? Gord Dibben MS Excel MVP On 21 Feb 2007 10:19:26 -0800, "MarkyMark" wrote: Is there away to create a macro to put numbers in one row with comma between them instead having them in 3000 rows? From A 1 5656565656 2 5353535353 3 6262626262 4 3535353535 5 2326235623 To A 1 "5656565656","5353535353","6262626262","3535353535 ","2326235623" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
Thank you all i have 2007 office
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma
One cell in 2007 is pretty much the same as one cell in earlier versions. to
the best of my knowledge, the limit is still 32K. Nonetheless, I gave you a macro to try it. -- Regards, Tom Ogilvy "MarkyMark" wrote: Thank you all i have 2007 office |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract the text between last comma and last but one comma. | Excel Worksheet Functions | |||
Comma Delimited-need comma at beginnng & end | Excel Discussion (Misc queries) | |||
New row after every comma | Excel Discussion (Misc queries) | |||
Comma | Excel Worksheet Functions | |||
Excel How do I create a comma delineated xls file to a comma delineated. | Excel Discussion (Misc queries) |