Help

Re: More robust formula to eliminate quotes in rollup fields?

Solved
Jump to Solution
740 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Rasha
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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.

image

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:

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

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