I'm trying to construct a formula to concat text in a range of cells without
listing the cells individually in a comma-separated list, so that the formula
automatically takes into account newly inserted cells. concatenate(x1:x3)
results in a #VALUE error.

Any advice?

Thanks,
Marc

=CONCATENATE(X1,X2,X3)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marc Hebert" <Marc wrote in message
...
I'm trying to construct a formula to concat text in a range of cells
without
listing the cells individually in a comma-separated list, so that the
formula
automatically takes into account newly inserted cells. concatenate(x1:x3)
results in a #VALUE error.

Any advice?

Thanks,
Marc

Marc

CONCATENATE function will not take a range.

This User Defined Function will.

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

Usage is: =ConCatRange(X1:X3)

As rows or cells are inserted betweenX1 and X3, the X1:X3 will change to
accommodate.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown above.


Gord Dibben Excel MVP




On Fri, 15 Dec 2006 11:03:01 -0800, Marc Hebert <Marc
wrote:

I'm trying to construct a formula to concat text in a range of cells without
listing the cells individually in a comma-separated list, so that the formula
automatically takes into account newly inserted cells. concatenate(x1:x3)
results in a #VALUE error.

Any advice?

Thanks,
Marc

Gord Dibben MS Excel MVP

Note:

As written the concatenated text will be comma de-limited.

Change If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," to " " for
space delimited.


Gord

On Fri, 15 Dec 2006 11:34:15 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Marc

CONCATENATE function will not take a range.

This User Defined Function will.

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

Usage is: =ConCatRange(X1:X3)

As rows or cells are inserted betweenX1 and X3, the X1:X3 will change to
accommodate.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as shown above.


Gord Dibben Excel MVP




On Fri, 15 Dec 2006 11:03:01 -0800, Marc Hebert <Marc
wrote:

I'm trying to construct a formula to concat text in a range of cells without
listing the cells individually in a comma-separated list, so that the formula
automatically takes into account newly inserted cells. concatenate(x1:x3)
results in a #VALUE error.

Any advice?

Thanks,
Marc

Gord Dibben MS Excel MVP