Combine multiple email addresses (in separate cells) into one cell (separated by comma) Google Sheets or Excel

I'm trying to combine multiple cells with email addresses into one cell where emails are separated by commas.

I've used this formula:

=P2 & ", " & Q2 & ", " & R2 & ", " & AD2 & ", " & AE2 & ", " & AF2

But only two cells have emails, so the result is this:

, name1@domain.com, , , name2@domain.com,

How do I fix this? (Remove the redundant commas; bypass the empty cells; other solution.)

maciej_

Posted 2016-07-27T01:55:55.537

Reputation: 23

put spaces properly to make the formular readable – phuclv – 2016-07-27T03:28:52.637

Answers

You could use IF functions as follows:

=IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,"")

This has a possibility of having an extra comma at the end, which you can remove using the following:

=IF(RIGHT(string,1)=",",LEFT(string,LEN(string)-1),string)

where string is the output of the previous formula (or any other string).

Rolled up into one ugly formula, it would look like this:

=IF(RIGHT(IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,""),1)=",",LEFT(IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,""),LEN(IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,""))-1),IF(P2<>"",P2&", ","") & IF(Q2<>"",Q2&", ","") & IF(R2<>"",R2&", ","") & IF(AD2<>"",AD2&", ","") & IF(AE2<>"",AE2&", ","") & IF(AF2<>"",AF2,""))

Alexis Olson

Posted 2016-07-27T01:55:55.537

Reputation: 235

Thank you so much, Alexis, for taking the time!! That's exactly what I needed, I appreciate it. – maciej_ – 2016-07-28T18:54:01.030

Option 1: First, adds helper columns for each possible mail address column, use IF formula to get comma appended to each non-empty string, otherwise empty string is returned. For example, formula of helper column for P2 is =IF(P2<>"", P2&",", ""). Then concatenates above helper column together.

Option 2: Concatenates all your string as you did before, then substitutes multiple commas into one comma use regex, as suggested at https://stackoverflow.com/questions/26280008/excel-how-do-i-replace-text-by-using-a-wildcard

Yingyu YOU

Posted 2016-07-27T01:55:55.537

Reputation: 199