SUBSTITUTE String Help


#1

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


#2

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.


#3

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