Skip to main content

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

Did you try specifying the separator?


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

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.


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.

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.


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



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


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

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



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

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

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

Reply


Terms & Conditions