Regex or Subsitute Formula? Or Both?

[LeighAnnMoltz]

I have a field that has a ton of linked records and I'm trying to format that into a nice list for use in the Pages extension.  

It's generally coming through with commas and quotation marks separating it. 

I found this formula in another thread, which seems to remove the opening quote marks, but not subsequent. 

IF(LEFT({Marketing Tactic - Detail}, 1) = '"',

SUBSTITUTE(

MID({Marketing Tactic - Detail}, 2, LEN({Marketing Tactic - Detail}) - 2),

'""',

'"'

),

{Marketing Tactic - Detail}

)

I'm trying to format something like this:

"Digital Signage (BOH) - TVs installed to reach branch salespeople in lunch rooms, warehouses and other strategic locations. ", Email to Salespeople - Email to increase brand or product awareness with salespeople usually offering a stocking incentive. , Flyer for Salespeople - Flyer used for vendor to increase awareness about products or stocking offers available to salespeople., Post on Intranet - Announcement posted on the intranet website to increase awareness about products or stocking offers.

to something like this: 

Digital Signage (BOH) - TVs installed to reach branch salespeople in lunch rooms, warehouses and other strategic locations.

Email to Salespeople - Email to increase brand or product awareness with salespeople usually offering a stocking incentive.

Flyer for Salespeople - Flyer used for vendor to increase awareness about products or stocking offers available to salespeople.

Post on Intranet - Announcement posted on the intranet website to increase awareness about products or stocking offers.

I can handle hard returns, I prefer if I can use bullets or hyphens or something to designate the list elements. 

Thank you in advance!!!

[Ben_Young1 · Accepted solution]

Hey @LeighAnnMoltz! 

Based on the information you provided, I put together the following example:

 

To do this, I implemented a rollup field with the following formula:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n• "
                )
            )
        )
    )
)

You can define a custom separator by replacing the dot symbols in the formula. If you want a hard break between each item, then you can insert an additional new line operator like this:

 

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n\n• "
                )
            )
        )
    )
)

[LeighAnnMoltz]

Your sample accomplishes EXACTLY what I'm trying to do!! Thank you.  

My implementation is incorrect. This is what it looks like:  

 This is how I plugged in your formula:  

IF(

{Marketing Tactic - Detail},

IF(

COUNTALL({Marketing Tactic - Detail}) = 1,

"• " & TRIM("" & {Marketing Tactic - Detail}),

IF(

COUNTALL({Marketing Tactic - Detail}) > 1,

"• " &

TRIM(

ARRAYJOIN(

{Marketing Tactic - Detail}, "\n• "

)

)

)

)

)

What did I mess up? 

[LeighAnnMoltz]

Wait .... I think it may the "rollup" .... I simply added a formula field for this. If I add a rollup field though, I can't input a formula. So perhaps this is a very silly question. How do I do that? 

[Ben_Young1]

Instead of a formula field, you need to create a rollup field.
If you use the rollup field, you can just copy and paste the exact formula I posted; you won't have to change it at all.

Here are some screenshots for additional context:

 

 

After saving the field, we get:

 

[LeighAnnMoltz]

I think I understand. You're able to use "rollup" because those multi-selects are records in another table that you're linking. Thus, rollup.  

The way I currently have the data set up in this table is that it's just a multiple select field. This is a huge table .... I can change it if that's my only option, but I really prefer not. Is there a way to accomplish this without the rollup/linked record requirement or not really?

[Ben_Young1]

Ouch. That's rough. If I had to nominate a field to indiscriminately eject from databases and data structures, it would be multiple select fields. In 99% of use cases that have a multiple select field implemented, the better choice would have actually been a linked record field.
Based on the data you were working with, I strongly advise that you change your multiple select field into a linked record field.
It's not nearly as hard as it might seem, and it will make your database so much more sustainable and resilient.

You can change the field type into a linked record field and Airtable will automatically convert your preexisting multiple select options into new records in the table that's created.
I've included a quick video snippet of how quickly and cleanly it can be done.

If you watch that video and still decide that it's not worth it for you, then let me know and I'll figure a formula for your multiple select-based use case.

[LeighAnnMoltz]

I decided to bite the bullet and switch it over while I was waiting lol only took a few minutes, I just didn't want another sheet in the base. It's for the best! Thank you for your help. This is exactly, exactly, exactly what I needed. I wouldn't have figured that formula out!  

[kuovonne]

Glad you got it figured out. I also agree that having a linked record in a different table instead of a multi-select is much nicer.

In case you are interested, I tend to use a slightly different rollup formula. If you know there will be no empty lines, you can omit the ARRAYCOMPACT().

IF(

  COUNTALL(ARRAYCOMPACT(values)),

  "- " & ARRAYJOIN(ARRAYCOMPACT(values), "\n\n- ")

)

[Michelle_Koch]

It's a year+ since this thread was started, but you just solved an issue for me that I had been banging my head against for two days.

And I learned something really useful about Rollup fields. THANK YOU!!