Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can a function do this?
This is a request for a friend. He has a long list on numbers (usually 10
digits) in a column. Let's say A1:A300. Is there a formula that will enter all of those numbers into one cell consecutively separated by commas and no spaces? I guess the question also begs the question of how much information you can place in one cell. I am unsure of the Excel version although I am sure it is a late one if not 2003. Thanks in advance... Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can a function do this?
Bob
I will provide a macro and a User Defined Function. Use either as you choose. To use the macro, just run ConCat_Cells from ToolsMacroMacros Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub To use the UDF, enter in a cell =ConCatRange(A1:A30) Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function Both the Macro and the Function would be copy/pasted to a general module in the workbook. If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo Gord Dibben MS Excel MVP On Tue, 13 Jun 2006 21:41:15 -0400, "Bob Newman" wrote: This is a request for a friend. He has a long list on numbers (usually 10 digits) in a column. Let's say A1:A300. Is there a formula that will enter all of those numbers into one cell consecutively separated by commas and no spaces? I guess the question also begs the question of how much information you can place in one cell. I am unsure of the Excel version although I am sure it is a late one if not 2003. Thanks in advance... Bob Gord Dibben MS Excel MVP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can a function do this?
A cell can contain about 32k characters--but only about 1000 characters will be
visible in the cell (unless you add some alt-enters every 80-100 characters. One way to put all those cells into one cell (but all the characters won't be viewable): Put this in B1: =a1 Put this in B2: =b1&","&A2 and drag down the column. select that last cell and edit|copy edit|Paste special|values And you can clear the cells B1:b299. ========== If you want to see all the numbers in that last cell, you could add the alt-enters (char(10)'s) every so often. In B1: =A1 (no change) In B2: =B1&","&IF(MOD(ROW(),7)=0,CHAR(10),"")&A2 And drag down Convert to values and clean up those other cells. And format that cell nicely. Widen the column format|cells|alignment tab|check Wrap Text Deepen the row height if you have to. Bob Newman wrote: This is a request for a friend. He has a long list on numbers (usually 10 digits) in a column. Let's say A1:A300. Is there a formula that will enter all of those numbers into one cell consecutively separated by commas and no spaces? I guess the question also begs the question of how much information you can place in one cell. I am unsure of the Excel version although I am sure it is a late one if not 2003. Thanks in advance... Bob -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |