Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Remove commas without data

Topic Labels: Formulas
219 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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 !

4 Replies 4

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…

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 !

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.

Thanks you !! Works like a charm :slightly_smiling_face: