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
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
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 <[email protected]> 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
On Sat, 1 Jul 2006 11:20:02 -0700, Ed <[email protected]> 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
Thank you!
On Mon, 3 Jul 2006 06:35:02 -0700, Ed <[email protected]> wrote:
>Thank you!
You're welcome. Glad to help
--ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks