Concatenate an Area (Range)

[Gilles P (FR)]

Hello,

I like concatenate Cells A1 to A3 by using a range "{=Concatenate(A1:A3)}"

I can obtain a by matrix formula a result as
{"valueA1"."ValueA2"."ValueA3"}, but i can't convert it in a text chain
"valueA1ValueA2ValueA3"

I there is a way by formula of matrix calculation ?
If not could you please give me the VBA code to create a function ?

Thanks

Gilles P. (FR)

 

[Billy Liddel]

Don't know a formula but a simple UDF (no error checking)

Function concat(data) As String
Dim c, str As String
For Each c In data
If c = data(1) Then
str = c
Else
str = str & " " & c
End If
Next c
concat = str
End Function

Hope this helps.

Peter Atherton

 

[wknehans]

To use the CONCATENATE function, you can't just specifiy a range, you have to
give it each bit of data individually, separated by a comma. Try this:

=Concatenate(A1,A2,A3)

Should be a lot easier than a matrix calculation....

BD

 

[Gord Dibben]

Billy

Your function returns some strange results if any blank cells or duplicates
in the range.

Try this one.........

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


Gord Dibben MS Excel MVP

 

[Billy Liddel]

Mm!

Thanks Gord, I did not test it enough

Peter Atherton

 

[Gord Dibben]

Not testing enough is usually my gaffe.


Gord

Mm!

Thanks Gord, I did not test it enough

Peter Atherton