how to combine cell data for a range of cells

**dayton** · 08-12-2009, 12:34 PM

I'm trying to combine cell data for a range of cells. I can not use =A1&" "&b1&" "&c1& because I have too many to enter individually. Is there a way to do this for a range?

Thanks in advance for your help

**DonkeyOte** · 08-12-2009, 02:21 PM

Not with native functions, no, I'm afraid not.

Laurent Longre's morefunc.xll add-in provides the MCONCAT function which can concatenate ranges - else you're looking at building your own UDF (VBA).

(for download of morefunc see: http://download.cnet.com/Morefunc/30...-10423159.html)

**JBeaucaire** · 08-12-2009, 03:04 PM

MoreFunc is excellent, but it's a big ADDIN. If you're only interested in the String Concatenation capability, as Don indicated a UDF would be the way to go.

Here's a VERY simple one, that string concatenates a range with no criteria:

Please Login or Register  to view this content.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use. To use as a function, just enter something like this:

=CONCATRANGE(A1:C1, ",")

The first parameter is the range, the second parameter is the delimiter you want to use.

**DonkeyOte** · 08-12-2009, 03:22 PM

Another approach might be the below which avoids iteration on cell by cell basis (assumes all values are to be used):

Please Login or Register  to view this content.

Called say as:

=CONCATSTR(A1:A10,",")
=CONCATSTR(A1:B20,".")
etc

If it needs to be non-contiguous ranges you could consider using a ParamArray.

NOTE: the cell by cell approach obviously offers more flexibility long-term if there are certain requirements to be taken into account when reviewing the values themselves prior to adding to the concatenation...