Skip to content
Kuovonne's Guide to Airtable
- Pages
Share
Explore
Removing varying items from a list
Inspiration
In 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!
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.