Is there a function similar to concatenate that doesn't require each
individual cell to be referenced?

I have a database that has information in cells A1 through A1000. I
currently use conatenate(A1, .....,A1000). Is there a fundtion that will
return the same information - FUNCTION(A1:A1000)?

No functions of which I'm aware. A short macro would do it, tho

Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & rng.Text
Next
Range("B1") = strConcat
End Sub

For information on installing the code see
Getting Started with Macros and User Defined Functions

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



"Wildaz" wrote:

Is there a function similar to concatenate that doesn't require each
individual cell to be referenced?

I have a database that has information in cells A1 through A1000. I
currently use conatenate(A1, .....,A1000). Is there a fundtion that will
return the same information - FUNCTION(A1:A1000)?

Thank you. I was looking in the other forums and found a function to
download that does this. It is MCONCAT.

"Wildaz" wrote:

Is there a function similar to concatenate that doesn't require each
individual cell to be referenced?

I have a database that has information in cells A1 through A1000. I
currently use conatenate(A1, .....,A1000). Is there a fundtion that will
return the same information - FUNCTION(A1:A1000)?

On Tue, 14 Mar 2006 12:38:27 -0800, Wildaz
wrote:

Thank you. I was looking in the other forums and found a function to
download that does this. It is MCONCAT.

"Wildaz" wrote:

Is there a function similar to concatenate that doesn't require each
individual cell to be referenced?

I have a database that has information in cells A1 through A1000. I
currently use conatenate(A1, .....,A1000). Is there a fundtion that will
return the same information - FUNCTION(A1:A1000)?


To use MCONCAT, you'll need to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/
--ron

Ron Rosenfeld wrote...
....
To use MCONCAT, you'll need to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/

Note also that MCONCAT chokes (returns #VALUE!) if its result would
exceed 255 characters. If result strings could exceed 255 characters,
VBA is the only alternative.

On 14 Mar 2006 16:47:46 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
To use MCONCAT, you'll need to download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/

Note also that MCONCAT chokes (returns #VALUE!) if its result would
exceed 255 characters. If result strings could exceed 255 characters,
VBA is the only alternative.

Good point, and with a range the size the OP mentioned, he would likely exceed
255 characters.
--ron

Very nice Duke.........and it even works with non-contiguious ranges as well,
which I like very much. I would ask tho, if it could be modified to allow
the user to select the cell for the concatenation to be placed in each time,
maybe by pop-up, rather than the location being hard coded in.

TIA
Vaya con Dios,
Chuck, CABGx3



"Duke Carey" wrote:

No functions of which I'm aware. A short macro would do it, tho

Sub ConcatSelection()
Dim rng As Range
Dim strConcat As String

For Each rng In Selection
strConcat = strConcat & rng.Text
Next
Range("B1") = strConcat
End Sub

For information on installing the code see
Getting Started with Macros and User Defined Functions

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



"Wildaz" wrote:

Is there a function similar to concatenate that doesn't require each
individual cell to be referenced?

I have a database that has information in cells A1 through A1000. I
currently use conatenate(A1, .....,A1000). Is there a fundtion that will
return the same information - FUNCTION(A1:A1000)?

CLR wrote...
Very nice Duke.........and it even works with non-contiguious ranges as well,
which I like very much. I would ask tho, if it could be modified to allow
the user to select the cell for the concatenation to be placed in each time,
maybe by pop-up, rather than the location being hard coded in.
....

Gee, maybe even make it a udf (like the hundreds of arbitrary
concatenation UDFs that've been posted over the years). If the udf used
a ParamArray argument to allow for variable number of arguments, it'd
only be limited by formula length since it could be called repeatedly,
i.e., =udf(...)&udf(...)&...&udf(...).