How to remove the unnecessary commas?

Ashley Klaers

I worked out this formula with someone during a ProDesk session but they weren't sure how to remove the commas when they weren't needed so I'm hoping someone else maybe has an idea!

The formula:

=JOIN([Inspected Employee's Name 1]@row + ", " + [Inspected Employee's Name 2]@row + ", " + [Inspected Employee's Name 3]@row + ", " + [Inspected Employee's Name 4]@row + ", " + [Inspected Employee's Name 5]@row + [Inspected Employee's Name 6]@row + ", " + [Inspected Employee's Name 7]@row + ", " + [Inspected Employee's Name 8]@row + ", " + [Inspected Employee's Name 9]@row + ", " + [Inspected Employee's Name 10]@row + ", ")

Here's an example of what it looks like currently and I just don't know how to remove the extra commas without breaking the formula:

Carson Penticuff

When you are dealing with non-adjacent columns, the best bet is to just "brute force" the formula. This should work as long as the employees are always filled in order.

=IF([Inspected Employee's Name 1]@row <> "", [Inspected Employee's Name 1]@row, "") + IF([Inspected Employee's Name 2]@row <> "", ", " + [Inspected Employee's Name 2]@row, "") + IF([Inspected Employee's Name 3]@row <> "", ", " + [Inspected Employee's Name 3]@row, "") + IF([Inspected Employee's Name 4]@row <> "", ", " + [Inspected Employee's Name 4]@row, "") + IF([Inspected Employee's Name 5]@row <> "", ", " + [Inspected Employee's Name 5]@row, "") + IF([Inspected Employee's Name 6]@row <> "", ", " + [Inspected Employee's Name 6]@row, "") + IF([Inspected Employee's Name 7]@row <> "", ", " + [Inspected Employee's Name 7]@row, "") + IF([Inspected Employee's Name 8]@row <> "", ", " + [Inspected Employee's Name 8]@row, "") + IF([Inspected Employee's Name 9]@row <> "", ", " + [Inspected Employee's Name 9]@row, "") + IF([Inspected Employee's Name 10]@row <> "", ", " + [Inspected Employee's Name 10]@row, "")

che.rabajante

Hello @Ashley Klaers

Please try this:

=JOIN(COLLECT([Inspected Employee's Name 1]@row:[Inspected Employee's Name 10]@row, [Inspected Employee's Name 1]@row:[Inspected Employee's Name 10]@row, <> ""), ", ")

Hope this helps.

Che

Ashley Klaers

@che.rabajante

That sort of worked! It helped with the comma issue but its also generating all of the information between 1 through 10:

che.rabajante

Hello @Ashley Klaers

Just reference or adjust the column containing the employee names so it doesn't pick up all the information

=JOIN(COLLECT([Inspected Employee's Name 1]@row +... + [Inspected Employee's Name 10]@row, [Inspected Employee's Name 1]@row + ... + [Inspected Employee's Name 10]@row, <> ""), ", ")

Hope it helps

che

Ashley Klaers

A bit closer, unless I'm still missing something or not fully understanding. Now the commas are gone altogether and there's no space between the names:

=JOIN(COLLECT([Inspected Employee's Name 1]@row + [Inspected Employee's Name 2]@row + [Inspected Employee's Name 3]@row + [Inspected Employee's Name 4]@row + [Inspected Employee's Name 5]@row + [Inspected Employee's Name 6]@row + [Inspected Employee's Name 7]@row + [Inspected Employee's Name 8]@row + [Inspected Employee's Name 9]@row + [Inspected Employee's Name 10]@row, [Inspected Employee's Name 1]@row + [Inspected Employee's Name 2]@row + [Inspected Employee's Name 3]@row + [Inspected Employee's Name 4]@row + [Inspected Employee's Name 5]@row + [Inspected Employee's Name 6]@row + [Inspected Employee's Name 7]@row + [Inspected Employee's Name 8]@row + [Inspected Employee's Name 9]@row + [Inspected Employee's Name 10]@row, <>""), ", ")

Carson Penticuff

When you are dealing with non-adjacent columns, the best bet is to just "brute force" the formula. This should work as long as the employees are always filled in order.

=IF([Inspected Employee's Name 1]@row <> "", [Inspected Employee's Name 1]@row, "") + IF([Inspected Employee's Name 2]@row <> "", ", " + [Inspected Employee's Name 2]@row, "") + IF([Inspected Employee's Name 3]@row <> "", ", " + [Inspected Employee's Name 3]@row, "") + IF([Inspected Employee's Name 4]@row <> "", ", " + [Inspected Employee's Name 4]@row, "") + IF([Inspected Employee's Name 5]@row <> "", ", " + [Inspected Employee's Name 5]@row, "") + IF([Inspected Employee's Name 6]@row <> "", ", " + [Inspected Employee's Name 6]@row, "") + IF([Inspected Employee's Name 7]@row <> "", ", " + [Inspected Employee's Name 7]@row, "") + IF([Inspected Employee's Name 8]@row <> "", ", " + [Inspected Employee's Name 8]@row, "") + IF([Inspected Employee's Name 9]@row <> "", ", " + [Inspected Employee's Name 9]@row, "") + IF([Inspected Employee's Name 10]@row <> "", ", " + [Inspected Employee's Name 10]@row, "")

Ashley Klaers

I was able to get it to work with that one fully, thanks Carson! :)

Carson Penticuff

Happy to help!