More robust formula to eliminate quotes in rollup fields?

[Rasha]

I have a concatenate function in a table’s primary field. One of the fields it combines is a rollup of companies in a Companies table. Typically, it’s one company per record, occasionally two.

To strip the pesky quotes I get when there’s comma in a company’s name (e.g. Habor Lights, Inc.), this formula only partially works:

SUBSTITUTE({COMPANIES}&"", '"', '')

When two companies are rollup up comma-separated, the formula strips that comma which is in fact needed

Company 1: Toyota Research Institute
Company 2: Abbot Ventures

Rollup: Toyota Research Institute, Abbot Ventures
With formula applied: Toyota Research InstituteAbbot Ventures

Can I improve on this formula or is there a better approach altogether?

---

Edit: tried the in-rollup approach suggested in this thread – Close, but no space between company names.

SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(Companies)), '"', '')

Result: Toyota Research Institute,Abbot Ventures

[Kamille_Parks]

Did you try specifying the separator?

SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(Companies), ", "), '"', '')

[Rasha]

Thanks for your input! I hadn’t, but just did.

There’s a hiccup in instances where there isn’t a Company match to a Contact - an arbitrary coma is inserted. In the example below, Test Contact 2 is not associated with a company.

I’m sure this is an IF statement - I’m not terrific with these but working on it.

[Kamille_Parks]

Then you need to have ARRAYCOMPACT in there as well. ArrayJoin has to be on the outside, it should not matter whether you put Compact outside or inside Unique.

[Rasha]

Yup! Thanks you. Both do work but I lose the space between company names:

SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(Companies), ", “)), '”', ‘’)

SUBSTITUTE(ARRAYJOIN(ARRAYCOMPACT(ARRAYUNIQUE(Companies), ", “)), '”', ‘’)

[Kamille_Parks · Accepted solution]

Your nesting isn’t right. The closing parenthesis for Unique and Compact should be right next to each other. Right now, you aren’t specifying a separator for Join because its in nested improperly inside another array function for both examples you showed.

SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(Companies)), ", "), '"', '')

[Rasha]

Ah! Of course. Thanks for your help on this.