Skip to main content

SUBSTITUTE String Help

M
Forum|alt.badge.img+6

This is probably pretty simple for someone out there. I’m not really a database guy, just hacking my way through creating a product information system for thousands of styles to make catalog production easier.

I need to do a find and replace using the Substitute formula string but can’t get it working.

In one field I have a bunch of product names. An example would be something like “LS Shirt” but I need to change it to “Long Sleeve Shirt”. How do I go about it?

The tips don’t go into much detail about how to identify how to reference a specific column/table and then have it changed.

Open to any help or tips…

Thanks,

Matt

    3 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • January 4, 2019

    There’s a reason for that…

    In Airtable, formulas determine the value of the field that contains the formula. For instance, if your produce name field was called {ProductName}, you could define a second, formula field called, say, {ProductName - Cooked} which would be a duplicate of {ProductName} except for having every instance of ‘LS Shirt’ changes to read ‘Long Sleeve Shirt’. You would do so by defining the following formula for {ProductName - Cooked}:

    'SUBSTITUTE({ProductName},‘LS Shirt’,‘Long Sleeve Shirt’)`

    To make additional substitutions, you would use nested SUBSTITUTE() functions:

    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {ProductName}.
            'LS Shirt','Long Sleeve Shirt'
            ),
        'SS Shirt','Short Sleeve Shirt'
        ),
    'SL Shirt','Sleeveless Shirt'
    )

    The indentation is arbitrary; I try to find something that helps me to understand the process flow and simplifies trouble-shooting. If you’re creating a formula containing more than a handful of nested SUBSTITUTES()s, you’re probably best off editing the formula offline — that is, not directly using Airtable’s formula editing window but in a standalone text editor — and then cutting and pasting it into Airtable. Your standalone editor could be as simple as Notepad, but most people use some sort of code-savvy editor that can help one keep track of suck things as having too few (or too many) closing parentheses or trailing commas. (On Windows, I’m a big fan of Notepad++.)

    The maximum length formula supported is, to use a technical term, pretty damn big. (There’s no fixed length, because it varies as a function of number of characters and number of functions contained; in my experience, it’s been long enough to contain a short story but not quite long enough to hold a novella.) In any case, you’ll have room for quite a few SUBSTITUTE()s, if needs be.

    B
    M
    J
    4 people like this
    • B
      bdelanghe
    • M
      Matt_Frederick
    • J
      Jay_Espin
    • V
      Vignisson
    M
    Forum|alt.badge.img+6
    • Matt_Frederick
    • Author
    • Known Participant
    • 12 replies
    • January 7, 2019
    W_Vann_Hall wrote:

    There’s a reason for that…

    In Airtable, formulas determine the value of the field that contains the formula. For instance, if your produce name field was called {ProductName}, you could define a second, formula field called, say, {ProductName - Cooked} which would be a duplicate of {ProductName} except for having every instance of ‘LS Shirt’ changes to read ‘Long Sleeve Shirt’. You would do so by defining the following formula for {ProductName - Cooked}:

    'SUBSTITUTE({ProductName},‘LS Shirt’,‘Long Sleeve Shirt’)`

    To make additional substitutions, you would use nested SUBSTITUTE() functions:

    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {ProductName}.
            'LS Shirt','Long Sleeve Shirt'
            ),
        'SS Shirt','Short Sleeve Shirt'
        ),
    'SL Shirt','Sleeveless Shirt'
    )

    The indentation is arbitrary; I try to find something that helps me to understand the process flow and simplifies trouble-shooting. If you’re creating a formula containing more than a handful of nested SUBSTITUTES()s, you’re probably best off editing the formula offline — that is, not directly using Airtable’s formula editing window but in a standalone text editor — and then cutting and pasting it into Airtable. Your standalone editor could be as simple as Notepad, but most people use some sort of code-savvy editor that can help one keep track of suck things as having too few (or too many) closing parentheses or trailing commas. (On Windows, I’m a big fan of Notepad++.)

    The maximum length formula supported is, to use a technical term, pretty damn big. (There’s no fixed length, because it varies as a function of number of characters and number of functions contained; in my experience, it’s been long enough to contain a short story but not quite long enough to hold a novella.) In any case, you’ll have room for quite a few SUBSTITUTE()s, if needs be.


    Excellent, thanks for the advice. Will give it a go this week!

    J
    Forum|alt.badge.img+3
    • Jay_Espin
    • New Participant
    • 2 replies
    • May 11, 2023
    W_Vann_Hall wrote:

    There’s a reason for that…

    In Airtable, formulas determine the value of the field that contains the formula. For instance, if your produce name field was called {ProductName}, you could define a second, formula field called, say, {ProductName - Cooked} which would be a duplicate of {ProductName} except for having every instance of ‘LS Shirt’ changes to read ‘Long Sleeve Shirt’. You would do so by defining the following formula for {ProductName - Cooked}:

    'SUBSTITUTE({ProductName},‘LS Shirt’,‘Long Sleeve Shirt’)`

    To make additional substitutions, you would use nested SUBSTITUTE() functions:

    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            {ProductName}.
            'LS Shirt','Long Sleeve Shirt'
            ),
        'SS Shirt','Short Sleeve Shirt'
        ),
    'SL Shirt','Sleeveless Shirt'
    )

    The indentation is arbitrary; I try to find something that helps me to understand the process flow and simplifies trouble-shooting. If you’re creating a formula containing more than a handful of nested SUBSTITUTES()s, you’re probably best off editing the formula offline — that is, not directly using Airtable’s formula editing window but in a standalone text editor — and then cutting and pasting it into Airtable. Your standalone editor could be as simple as Notepad, but most people use some sort of code-savvy editor that can help one keep track of suck things as having too few (or too many) closing parentheses or trailing commas. (On Windows, I’m a big fan of Notepad++.)

    The maximum length formula supported is, to use a technical term, pretty damn big. (There’s no fixed length, because it varies as a function of number of characters and number of functions contained; in my experience, it’s been long enough to contain a short story but not quite long enough to hold a novella.) In any case, you’ll have room for quite a few SUBSTITUTE()s, if needs be.


    This still works, just remove the extra dot at the end of > {ProductName}.

    Thanks for the formula 🤖

    Reply


    Most helpful members this week

    Terms & Conditions