How To pass a range of cells to CONCATENATE?

[Paladin]

I have a string of text broken up in a range of contiguous cells. I'd like to use the concatenate function, but pass it a range parameter "=concatenate(A1:An)" instead of a list of "n" cell parameters "=concatenate(A1, A2, A3, A4, ...An)"

Anyone know a way to work around that? If you pass it a range as a parameter, it only takes the string from the first cell in the range, so in my example above, the output is the string that was in A1.

The problem is, the size of the range will vary from case to case, so it's a case where i need to pass a dynamically determined relative range reference, as opposed to a known number of parameters.

 

[Aladin Akyurek]

Try MCONCAT of the morefunc.xll add-in.

 

[Paladin]

thanks!
Ok, downloaded the addin and finally got it attached...dumb MS security :P

Now I'm not quite sure how to get the syntax right for my needs, I keep getting a #Value! error...

here is my "testing" line direct from help... before tackling the big one...
=MCONCAT("Stream population for ",A2:A3," is ",A7,"/mile")

I can get a single multiple range to work (a2:a6), but when I try an creat a more complex string or multiple range effect.. I get the #value.

 

[Norie]

Does this work?

="Stream population for " & MCONCAT(A2:A3) & " is " & A7 & "/mile"

 

[Paladin]

Very kewl!

I love this forum.. you guys rock!

Thanks for the fast help... I'm gonna give you all props when I deliver the product