Concatenate a strings range.

[y]

CONCATENATE accepts only single cells.

How can I concatencate contiguos cells (a range of strings) without writing each cell reference by hand?

Only via VBA? And how?

Thank you Alex.

 

[Frank Kabel]

Hi Alex
one way:
1. download the free add-in Morefunc.xll . It includes the
function
MCONCAT (http://longre.free.fr/english/)

2. Use the formula
=MCONCAT(A1:E1,", ")

-----Original Message-----
CONCATENATE accepts only single cells.

How can I concatencate contiguos cells (a range of

strings) without writing each cell reference by hand?

 

[Tim Zych]

Don't know about any built in function that will do that. A macro way:

Dim cell As Range, str As String
Dim delim As String
delim = ","
For Each cell In Range("A1:A100").Cells
If Len(cell.Value) > 0 Then
str = str & cell.Value & delim
End If
Next cell

If Len(str) > 0 Then
str = Left(str, Len(str) - Len(delim))
End If

CONCATENATE accepts only single cells.

How can I concatencate contiguos cells (a range of strings) without

writing each cell reference by hand?

 

[Peter Huang]

Hi Alex,

Did Frank and Tim's suggestion help you?
I think for now, if you do not want to use othe addins to achieve your aim,
I agree with Tim's suggestion. We may need to write an funciton with VBA to
handle the range of cells' text.

If you still have any concern on this issue, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.