Remove commas without data

[NicoMG]

Hello,

I’m trying to manipulate a multi-select field in a formula and I’m having a problem.

I have for example:
Data1,Data2,Data3,Data4,

I would like to be able to transform this data to have:
Data1, Data2, Data3, Data4

I tried with SUBSTITUTE, REGEX…

Can you help me ? Thanks !

[kuovonne]

Welcome to the Airtable community!

Could you post screen shots including field headings, sample field values, and your current formula? That will help identify the issue. Normally a multi-select in a formula will not have a comma at the end. It also looks like you want to add spaces between select values…

[NicoMG]

Hello kuovonne!
Thank you for the welcoming!

Actually I am trying to remove values ​​from a multi-select (lookup from another table) with a formula

The field that removes values ​​I don’t want (I want to remove anything that doesn’t start with "Secteur - "):

(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Qualif- Mots clés et compétences (from CVthèque)}, "Secteur - B2C", "B2C"), "Figma",""), "Secteur - Energie", "Energie"), "API", "")

And I get:

B2C,,Energie,,

And that’s where I’m stuck at the moment, I can’t remove the extra commas
Thank you for you precious help !

[kuovonne]

NicoMG:

Actually I am trying to remove values ​​from a multi-select (lookup from another table) with a formula

Ah, that makes more sense. You can try something like this.

REGEX_REPLACE(
    REGEX_REPLACE(
        REGEX_REPLACE(
            REGEX_REPLACE(
                REGEX_REPLACE(
                    {Qualif- Mots clés et compétences (from CVthèque)},
                    "Figma|API",
                    ""
                ),
                "Secteur - ",
                ""
            ),
            "(, )+",
            ", "
        ),
        ", $",
        ""
    ),
    "^, ",
    ""
)

There are five nested REGEX_REPLACE functions.

• The innermost REGEX_REPLACE removes the multi-select values that you want to omit completely. Note that you need to include each of the values separated by pipes | in the expression.
• Working outwards, the next REGEX_REPLACE removes the prefix Secteur - from values.
• Working outwards, the next three REGEX_REPLACEes removes any duplicate, trailing, and leading commas and spaces…

There are probably other formulas that can accomplish this same thing. This is just one possibility.

[NicoMG]

Thanks you !! Works like a charm :slightly_smiling_face: