Concatenation Function

I have data in columns A2 through BK2 and I want to concatenate these into one cell. My problem is I need a comma and space between each concatenated item.

Example: If I have John in A2, William in B2 and Doe in C2 I want to concatenate them but....

instead of looking like this:

JohnWilliamDoe

It would look like this:

John, William, Doe

Is there a simple way to do this other than make a new column full of spaces and a new column full of commas and concatenating those between each cell? The way I have it setup I have placed the columns of spaces and texts but I am going to have to do =CONCATENATE(A2,BM2,BN2,B2,BM2,BN2,C2........all the way through BK2. Surely there is a simpler way or am I stuck?

You can include the comma and space in your concatenation formula

e.g. =A2&", ",B2&", "&C2&", ", etc...

but that would be quite long if you need to concatenate everything from column A to Bk

You can use a UDF.

So if you paste this code in your VB editor (Alt+F11, Insert|Module)

Please Login or Register  to view this content.

then use formula:

=aconcat(A2:BK2,", ")

that should get you quicker results.

In both cases, yes you could.

Yes, use this formula with the UDF above:

=SUBSTITUTE(TRIM(aconcat(IF(A2:BK2<>"",A2:BK2,"")," "))," ",", ")

Confirm this formula with CTRL+SHIFT+ENTER keys and you will see { } brackets appear around the formula, then copy down.