Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate an array
Hello I guess it might not possible, but I have around 100 columns each with
a single character, I would like to concatenate those into a single cell, is that possible in ANY way? thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate an array
100 columns and how many rows?
This UDF will do the trick but after about 1024 characters in a cell you won't see the text. 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 & "," 'remove the & "," if you don't want a comma Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function Usage is: =ConCatRange(A1:CV1) Gord Dibben MS Excel MVP On Sat, 1 Jul 2006 11:20:02 -0700, Ed wrote: Hello I guess it might not possible, but I have around 100 columns each with a single character, I would like to concatenate those into a single cell, is that possible in ANY way? thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate an array
On Sat, 1 Jul 2006 11:20:02 -0700, Ed wrote:
Hello I guess it might not possible, but I have around 100 columns each with a single character, I would like to concatenate those into a single cell, is that possible in ANY way? thanks How many rows in each column? You can do it with a simple UDF: ====================== Option Explicit Function Concat(rg As Range) As String Dim c As Range For Each c In rg Concat = Concat & c.Text Next c End Function ======================== <alt<F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code above into the module that opens. On the worksheet, enter =Concat(rg) where rg is the range of cells you wish to concatenate. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate an array
Thank you!
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate an array
On Mon, 3 Jul 2006 06:35:02 -0700, Ed wrote:
Thank you! You're welcome. Glad to help --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a concatenate text list by referencing an array | Excel Discussion (Misc queries) | |||
Concatenate Multiple Instances in Array | Excel Worksheet Functions | |||
How do I concatenate the contents of an array in Excel? | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |