View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.newusers
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default howto: concat (x1:x3)

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