Advanced Concatenate with spaces separating numbers

[naynay]

Hi,

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?

my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html
but I am using numbers and would like to separate them with spaces.

Thanks!

[Bob Phillips]

Try this UDF

Please Login or Register  to view this content.

[naynay]

Hi Bob,

Thank you so much for your help. I am a newbie when it comes to functions. Where do I put that code? I am using Excel 2007.

Thanks again.

[martindwilson]

download and install more functions add in
http://www.download.com/Morefunc/300...-10423159.html
then use mconcat however this seems limited to about 88 cells but you can get around that by
=MCONCAT(A1:A65," ")&" "&MCONCAT(A65:A130," ")

[Richard Schollar]

Using MCONCAT the output string is limited to 256 characters due I believe to limitations imposed by the C API used to shovel information back and forth between the xll functions and Excel.

Richard

[Bob Phillips]

Hi Bob,

Thank you so much for your help. I am a newbie when it comes to functions. Where do I put that code? I am using Excel 2007.

Thanks again.

Go to the VBIDE, Alt-F11, and insert a code module Insert>Module, and paste the code in. Close the VBIDE and in Excel, use

=CONCAT(C6,B6,B7,B8,B9)

[naynay]

Bob,

Thank you so much, it worked like a charm.

[shishpal1000]

sorry i could not use it.......

[martindwilson]

could not use what? please start your own thread ,linking to this one if you want,with a proper title

[holly92]

Hi, I had a similar problem and this solution worked a charm too, thanks