Help

Commas between emails in a concatenate

Topic Labels: Formulas
Solved
Jump to Solution
75 4
cancel
Showing results for 
Search instead for 
Did you mean: 

My brain is broken (or my formula is)…

We have 4 departments (Production, Creative, Marketing, BD). When a prospective client is in our radar we run their product through a review process to get a sense from a variety of perspectives, the quality of their work.

We submit the materials into an interface and the head of each department assigns a member from their team to the review. Once all 4 heads of assigned and marked their department ‘ready’ an automation kicks in and sends out an email… Sometimes there is 1 person assigned from each department… sometimes there are 2 or more…

So my sheet has (for each department)

finally though, I have a formula feild that attempts to combine all those emails into one string of comma separated emails to that it can be inserted into the to: field of my outbound, automated email.

Problem is… that doesnt work…

When there is more than one person assigned from a department, the comma between departments doesnt exist…

What should look like : “graham@email.com, joe@email.com, david@email.com, charles@email.com, arnold@email.com, steve@email.com

… instead looks like “graham@email.com, joe@email.comdavid@email.com, charles@email.comarnold@email.com, steve@email.com

I can’t figure it out. Maybe my use of concatenate is misguided?

Here is the formula

CONCATENATE(IF({Marketing Reviewer(s) Email(s)}=0," “,{Marketing Reviewer(s) Email(s)} & “,”),IF({Biz Dev Reviewer(s) Email(s)}=0,” “,{Biz Dev Reviewer(s) Email(s)}& “,”),IF({Creative Reviewer(s) Email(s)}=0,” “,{Creative Reviewer(s) Email(s)}& “,”),IF({Production Reviewer(s) Email(s)}=0,” ",{Production Reviewer(s) Email(s)}& “,”))

Thanks in advance for any help

1 Solution

Accepted Solutions

Hi Graham. You need to use ARRAYJOIN on your lookup fields before cat’ing them together. Check it out.

See Solution in Thread

4 Replies 4

Posting a Follow up to this…

Going back to this… For each department we have

  • Field A = a look up field to assign the person to the review.
  • Field B = a look up field that then combines the emails for those people(and formats it in the manner expected “graham@email.com, joe@email.com
  • Field C = a third field that combines all those emails from all departments into one into one string of comma separated emails…

Well field C is broken…

If I have this formula in field C:

IF({Field B}=0," ",{FieldB}& “,”)

Field B could look like: graham@email.com, joe@email.com
But in field C it looks like “graham@email.comjoe@email.com”

Why???

Hi Graham. You need to use ARRAYJOIN on your lookup fields before cat’ing them together. Check it out.

Im sorry, Augmented. Im not following. Is there a video or article you can point me at?

I figured it out. Thanks augmented.

Labels