Formula Help Please! Concatenate columns with delimiter & no delimiter for blanks

Annette Sheehan

Hello - any help on this would be amazing.

Example worksheet attached and issue below:

I'm trying to concatenate 4 columns of emails to create one column of all emails seperated by a semi-colon. The email columns are not side by side and there is a mix of blanks in the email columns. Everything I tried is leaving the delimiter (semi colon) in the results if a cell is blank - I'm looking for no deliminator to show on the blanks.

In the attached sheet - the 4 email columns are columns E, H, N & O. And column K shows the formula results I'm trying to get.

Thank you!!

example.xlsx

Andrée Starå

Hi @Annette Sheehan

I hope you're well and safe!

Try something like this.

=
JOIN(COLLECT([Email 1]@row:[Email 2]@row, [Email 1]@row:[Email 2]@row, 
CONTAINS("@", @cell)), ";") + 
JOIN(COLLECT([Email 3]@row:[Email 4]@row, [Email 3]@row:[Email 4]@row, 
CONTAINS("@", @cell)), ";")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Annette Sheehan

@Andrée Starå - thank you!

I tried your formula and it looks great except for when one email or more is blank - we then lose the semi-colon between the emails.

Example showing if email #1 is missing, the result is no semi-colon between email #2 & #3:

TWO@gmail.comTHREE@gmail.com;FOUR@gmail.com

Is there a way to always have the semi-colon show between emails? Thanks again!

Annette Sheehan

Does anyone have any thoughts on solving the comma issue? Thank you!

Andrée Starå

@Annette Sheehan

Excellent!

Happy to help!

Yes, I missed that part.

Try this.

=
JOIN(COLLECT([Email 1]@row:[Email 2]@row, [Email 1]@row:[Email 2]@row, 
CONTAINS("@", @cell)), ";") + IF(OR([Email 3]@row <> "", [Email 4]@row <> ""), ";"
+ JOIN(COLLECT([Email 3]@row:[Email 4]@row, [Email 3]@row:[Email 4]@row, 
CONTAINS("@", @cell)), ";"))

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Annette Sheehan

@Andrée Starå - that worked really well!

I'm just left with one extra semi-colon in front of an email address when the scenario is only email #3 populated.

(example result - ;THREE@gmail.com)

This is a rarer scenario in the sheet so I still think its a good result - thank you!