More robust formula to eliminate quotes in rollup fields?

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

Did you try specifying the separator?

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

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.

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.

2 Likes

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

image

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

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

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)), ", "), '"', '')
1 Like

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.