adding contents from multiple cells together

[seraphin]

Hi, all
How do we add the content of multiple cells together ?
I am aware of the following function to combine cells "&" (e.g. c3&d3&e3 or c3&" "&d3&" "&e3) but
(a) I have multiple rows (with different cell numbers per row) that I need to repeat the function
(b) I want to add a space between the content of each cell
(see attachment for a better example)

Is there any "non-manual" (and hopefully non-VBA) way to combine cells?
(e.g. can we use "..", together with "&" in any way, to assign the range of cells I want to combine)
Any advice ?

[mehmetcik]

Your best bet is to end each entry with a space

then you can use the concat function.

Please Login or Register  to view this content.

Otherwise you could use a userdefined function to create modified concat function, but you do not want any VBA.

This will do what you want. If you use a space as your seperator, type:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Kevin#]

There is no text function that allows you to add together the strings for multiple cells and ranges. in the way you want.
Concatenate is like using the "&" - everything has to be added one item at a time.

VBA, on the other hand, is much more flexible. Use about 3 or 4 lines of code to join the text for hundreds of adjacent cells together and insert spaces inbetween.

[mehmetcik]

Sorry Kevin, the Concat function will take a range.

[seraphin]

@ mehmetcik.
Thanks a lot. One thing to add though, somehow my Excel (2010) does not exactly do the concat as you suggested - that is, it returns #NAME? when I tried to "concat (a3:d3)" directly. I have to do either "concat(a3,b3,c3,d3)" or "concat(transpose(a3:d3)) (+F9 thingy)". But "concat" clearly beats the "&". Thanks again.

[Kevin#]

@mehmetcik forgot that one! Getting a bit long in the tooth.