How to remove the unnecessary commas?

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:

Find more posts tagged with

Formulas

Accepted answers

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, "")

All comments

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.

Ashley Klaers

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

Carson Penticuff

Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.