Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific Formulas

Removing varying items from a list

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

this post⁠

in the Airtable community, the author has two text strings of words, and wants to remove all the words that appear in the second list from the first list.

⁠

Discussion

Introducing the pipe character to separates choices

To separate different choices in RegEx, use the pipe character | between the choices.

⁠

This gets close to the desired results with a few extra commas and spaces. We’ll take care of those later.

Converting {List B} to use Pipes

To get {List B} to look like the expression with pipes, we can replace the commas and spaces with pipes.

⁠

Since we don’t really need to do anything with the {List B with Pipes} field, we can nest it inside the {RegEx} formula field.

⁠

Deleting the extra commas and spaces

You may be tempted to use SUBSTITUTE() to replace the repeated commas and spaces, but we don’t want to replace all the commas, only the extra ones, and there might be more than one extra one. Instead, use RegEx to replace repeated comma and spaces with a single comma and space.

⁠

Let’s consolidate all the formulas together.

⁠

Testing cases with partial matches

Notice that any occurrence of a phrase in {List B} is removed from {List A}, even if it is only a part of the phrase in {List A}. To avoid this problem and only remove complete matches, we need to use delimiters around the entire phrases so that the expression can tell where phrases begin and end. I use curly braces as delimiters because they do not appear in either of the original lists and are not REGEX control characters.

⁠

When consolidating the formulas, the end result was too long to fit nicely in a single screen capture, so I am not including it. However, you can use the same nesting technique to consolidate them.

Additional considerations

The field types of {List A} and {List B} might be lookup fields for the person posting in the community. In that case, the commas do not actually exist, and a slightly different approach might be necessary.

If the original fields are lookups, I would convert them to rollup fields, and include the delimiters in the rollup formula.

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.