concatenate 30+ cells

  • Thread starter Thread starter Mathias
  • Start date Start date
M

Mathias

Hi all,
does anyone know a simple way to concatenate a range of
cells, e.g. A1:CA1, without typing 100 "&"s.

Thanks
Mathias
 
Hi Don,
I have around 200 columns that contain either "0" or "1".
After the last column I what to generate the 0100100
barcode of all the cells in that row. I can either use
=A1&B1&C1....., Or use a different worksheet and do
=sheet1!B2&A1 (and drag to the end). I think there must be
a simpler way of getting this done using an array formula
or something???
Any Ideas?
Thanks
 
Mathias,

Two ways.

First method:

In cell A2, use the formula
=A1

In cell B2, use the formul
=A2 & B1
Copy all the way to CA2, which will have your final answer.

Second method:

Use a user-defined-function:

Function myConcat(inRange As Range) As String
Dim myCell As Range
myConcat = ""
For Each myCell In inRange
myConcat = myConcat & myCell.Value
Next myCell
End Function


Used like:

=myConcat(A1:CA1)


HTH,
Bernie
MS Excel MVP
 
See if this idea helps

for each c in selection
mycode=mycode & c
next
msgbox mycode
 
Thanks a lot!!!
Mathias
-----Original Message-----
Mathias,

Two ways.

First method:

In cell A2, use the formula
=A1

In cell B2, use the formul
=A2 & B1
Copy all the way to CA2, which will have your final answer.

Second method:

Use a user-defined-function:

Function myConcat(inRange As Range) As String
Dim myCell As Range
myConcat = ""
For Each myCell In inRange
myConcat = myConcat & myCell.Value
Next myCell
End Function


Used like:

=myConcat(A1:CA1)


HTH,
Bernie
MS Excel MVP




.
Click to expand...
 
You can use a UDF (user defined function)... heres one
that seems to work:

Function ConcatALOT(MyRange As Range) As String
For Each MyRange In MyRange
ConcatALOT = ConcatALOT & MyRange.Value
Next
End Function

Then use it as =concatalot(a1:ca1) and your set...

Post back if you don't know how to use or create UDF


Cheers
Juan
 
does anyone know a simple way to concatenate a range of
cells, e.g. A1:CA1, without typing 100 "&"s.
Click to expand...

Since everyone else has provided an array concatenation udf, why not mine too?


Function mcat(ParamArray s()) As String
'Copyright (C) 2002, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
'------------------------------------
'string concatenation analog to SUM

Dim r As Range, x As Variant, y As Variant

For Each x In s

If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r

ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y

Else
mcat = mcat & x

End If

Next x

End Function


This takes a variable number of arguments, handles ranges, arrays (including
arrays of arrays if called by other VBA procedures), and single (scalar) values.
 
Back
Top