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 |
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 |
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 |
Concatenate an array
Thank you!
|
Concatenate an array
On Mon, 3 Jul 2006 06:35:02 -0700, Ed wrote:
Thank you! You're welcome. Glad to help --ron |
All times are GMT +1. The time now is 12:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com