Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this

 

Dear Barry

Does 'works absolutely perfectly' apply to your 15,000 rows of data.

In your PC, what is the length of the string in cell B2 when you have 15,000 rows.

According to Microsoft, the limit for cell contents is :- 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

Also, just out  of curiosity, please tell me how you are using this.

Regards

Derek +++

>________________________________
> From: Barry White <imtigerwords@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Friday, 16 March 2012, 16:32
>Subject: Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this
>
>
> 
>Guess what?
> 
>I Believe I had solved my own issue, check it out
> 
> 
>I did a web search on google, typing in these exact keywords "transpose numbers insert commas"
>and came up with this:
> 
>http://excel.bigresource.com/Track/excel-duxIwyTY/
>http://excel.bigresource.com/Column-of-data-to-one-cell-separated-by-comma-duxIwyTY.html
> 
> 
>I have a list of data in individual cells in one excel column thus...
>
>Item 1
>Item 2
>Item 3
>
>And I would like the data to read thus in one cell...
>
>Item1,Item2,Item3.
>
>Is there an excel function I can use to do this quickly and efficiently?
> 
>Sub concat()
>Dim LR As Long
>LR = Range("A" & Rows.Count).End(xlUp).Row
>Range("B2").Value = Join(Application.Transpose(Range("A2:A" & LR)), ",")
>End Sub
> 
>And it works absolutely perfectly! Wow I usually don't get that lucky with web searches, maybe I will go out and play the Lotto.  Anybody got a solid set of six digits for me?
> 
>Eric Lutz
>
>________________________________
>From: Paul Schreiner <schreiner_paul@att.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, March 15, 2012 5:25 PM
>Subject: Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this
>
> 
>We can probably help you do it, but I think you're going to have a problem:
>IF you're using Excel 2010, then the total number of characters you can display
>in a cell is 32,767
>(only 1024 can display in the cell, but 32,767 will display in the formula bar)
>
>Now, if you have 15,000 rows, and then numbers are ONLY two digits long, then
>you'll be ok,
>because that's only 30,000 characters.
>But if more than 2,767 of them are more than 2 characters long... you're in
>trouble...
>
>based on your samples, you'll only be able to concatenate 4,000 of your 15,000
>rows...
>
>So, maybe you need to look at another way of handling this...
>
>Paul
>-----------------------------------------
>"Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can." - John Wesley
>-----------------------------------------
>
>________________________________
>From: Barry White <imtigerwords@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Thu, March 15, 2012 2:44:07 PM
>Subject: Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to
>do this
>
> 
>Hello all,
> 
>I have a column of Excel data that is all numbers, about 15,000 or so, one
>number in each cell at present.
> 
>However, I want to turn that column into a row, where all the data can be seen
>from a single cell in that row.
> 
>Example, I want to turn this:
> 98164754
>15642829
>15636430
>15699463
>15600864
>15652140
> 
>Into this, no spaces, but a comma between each number:
> 
>98164754,15642829,15636430,15699463,15600864,15652140
> 
>Exactly how do I do this?  Because, I know one simply fact, I am NOT doing this
>manually.
> 
>Eric
>
>[Non-text portions of this message have been removed]
>
>[Non-text portions of this message have been removed]
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (19)

Recent Activity:

• New Members 12
• New Files 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___