How To pass a range of cells to CONCATENATE?

Paladin

New Member
Joined
Sep 15, 2005
Messages
10
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. :unsure:
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Paladin

New Member
Joined
Sep 15, 2005
Messages
10
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
Does this work?

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

Paladin

New Member
Joined
Sep 15, 2005
Messages
10
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 :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,284
Messages
5,571,307
Members
412,381
Latest member
RogerL
Top