Formula Help Please! Concatenate columns with delimiter & no delimiter for blanks
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!!
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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!
-
Does anyone have any thoughts on solving the comma issue? Thank you!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!