• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a formula to CONCATENATE a row or column of values....SOLVED

XLPadawan

Member
Here's my attempt to concatenate a large number of cells in a row using a CSE formula:

{=CONCATENATE(IF(B2:W2<>"",B2:W2,""))}

Unfortunately, that doesn't work. I'd like to have a formula that could be applied to cells in a column as well (with minor editing, of course).
 
Here's my attempt to concatenate a large number of cells in a row using a CSE formula:

{=CONCATENATE(IF(B2:W2<>"",B2:W2,""))}

Unfortunately, that doesn't work. I'd like to have a formula that could be applied to cells in a column as well (with minor editing, of course).
Hi,

There's a great and free Excel addin called MOREFUNC, link below. Part of that addin is a function called MCONCAT which does exactly what you want.

=MCONCAT(B2:W2)



http://en.kioskea.net/download/download-24205-morefunc-macro-add-in-for-excel
 

Thank you, Somendra! I worked with the method you shared and developed a workaround for this problem:
_______________________________________________________
| Since F9 replaces formulas with values, if your original data changes, then you must re-write the |
| CONCATENATE(TRANSPOSE(…)) again. |


First I get the addresses I need:

SUBSTITUTE(ADDRESS(ROW(B2),COLUMN(B2)),"$","")

The values I want to concatenate are in cells B2 to J2, so above formula gives:

___B__C___D__E__F__G__H__I___J_
3|
4| B2 C2 D2 E2 F2 G2 H2 I2 J2

Type =B4:J4 Press F9 to get:

={"B2","C2","D2","E2","F2","G2","H2","I2","J2"}

Replace { & } with ( and ), replace "," with , remove remaining " - THEN
put CONCATENATE in front and you have a reusable formula that you can drag down to concatenate multiple rows.
 
Now that I have a viable solution to my question, how do I mark this question as SOLVED? Or do questions just stay open in perpetuity?

By the way, I thank all of you who responded and put your valuable time and effort into helping me.
 
SOLVED? Or do questions just stay open in perpetuity?

All posts remain open for perpetuity so that others may browse and search, by marking your post as solved you are raising a flag to "if you have a problem similar to this here is an answer".

Post marked as solved.


.
 
I think the original poster only has a 2 or 3 hour window in which to edit the Subject and/or delete the post

Ninjas have access to do that at all times
 
Back
Top