Is there a way to combine text from multiple cells into one cell with a comma b/t?

[theresabirdoutthere]

Hi All,

Is there a formula or function in excel that would allow me to combine text from several cells into one cell with a comma in between the text?

So cell B2 says "He does well" and C2 says "Strong worker" and D2 says nothing, and E2 says "Doing Okay"

I would like that combined into A2 to look like this - "He does well, Strong worker, Doing Okay"

I believe I can do this "=B2&", "&C2" but I how do I do it for more cells? Also is there a way to ignore blank cells so there won't be a blank comma?

Please let me know. You all have been great so far. Thanks!!!!!!

[hoyasaxa215]

Try this:

Please Login or Register  to view this content.

[mikeTRON]

A2:

Please Login or Register  to view this content.

but you will likely want an if formula to determine if it is blank or not before adding a comma and a blank.

[theresabirdoutthere]

A2:

Please Login or Register  to view this content.

but you will likely want an if formula to determine if it is blank or not before adding a comma and a blank.

Is there a formula that could do that?

[mikeTRON]

I am sure there is a more elegant solution but I think this will be the most easy to understand:

Please Login or Register  to view this content.

It essentially checks if B2, then C2, then D2 then E2 are NOT nothing (where "" is nothing), if it is NOT nothing, then add it with a comma where needed.

[hoyasaxa215]

This should work regardless of blanks:

Please Login or Register  to view this content.

[mikeTRON]

This should work regardless of blanks:

Please Login or Register  to view this content.

Nice, that is a much more slick solution.

[theresabirdoutthere]

Hi All,

First BIG THANKS to all of you! You have been awesome for this basic excel user!!! Seriously I would have been lost without you!

I ended up using the following formula ...

=if(B2<>"",B2,"")&if(C2<>"",", "&C2,"")&if(D2<>"",", "&D2,"")&if(E2<>"",", "&E2,"") - The only issue I am seeing is that if cell B is empty the results display with a comma starting the comments. Is there any way to have this not happen?

unfortunately this one added addition commas that were needed - =SUBSTITUTE(TRIM(B2&" "&C2&" "&D2&" "&E2)," ",", ")

Again you all are amazing!!!!!

[mikeTRON]

So, can you post a sample dataset so we can see what you are working with?

It is usually faster for me to see the data, create the formula and give it back then for you to tell me you have a problem, I fix it. Then you begin telling me all the one-off things we need to correct afterwards.