When you think of a substitution formula, the concept of "find and replace" likely comes to mind. In Airtable, however, you can use substitutions to perform powerful workflows. Here are seven time-saving substitution formulas that demonstrate simple and creative ways to solve many common problems.
Remember: This article will focus on the SUBSTITUTE() function and how to use it creatively in a variety of formulas, but if you want to learn more about the other useful functions Airtable provides you can always check out the formula field reference help doc for a complete overview.
Substitution basics
Before jumping into the more complex formulas, let's cover the basics. Substitution formulas, naturally, utilize the SUBSTITUTE() function and it's important to know a few different ways you can use it before taking a deeper dive into its abilities. Let's take a look at one of the most basic use cases:
SUBSTITUTE({Location}, "CA", "California")
The formula above looks at the Location field in a given table, checks for the abbreviated state code "CA" and, if found, replaces it with "California" instead. Of course, you have to know the possibilities for the target field or you can end up with some strange results. For example, if the Location field contained "CAT" you'd find the substitute formula would produce "CaliforniaT" because it matched part of the text. To prevent this sort of problem, you can combine the SUBSTITUTE() function with another function. In this case, we only want to look at the first two letters in the field so we can use the LEFT() function to first remove any additional characters before handling the substitution:
SUBSTITUTE(LEFT({Location}, 2), "CA", "California)
That solution really only works in this specific use case, but it demonstrates how nesting other functions can make your substitutions more reliable so that they return the results you expect. You can even nest substitutions within substitutions to allow for multiple replacements:
SUBSTITUTE(SUBSTITUTE(LEFT({Location}, 2), "CA", "California), "NY", "New York")
That formula will replace "CA" with "California" but also "NY" with "New York" if the California replacement doesn't occur. When nesting formulas, the order of operation works just like it does with simple math: the function nested deepest in the formula runs first and then feeds that data to the function containing it. That process continues until reaching the outermost function. Make sure to remember this so your formula runs in the desired order.
Aside from nesting, substitutions can help other functions produce the results you need and we'll see several examples in the formulas that follow in this article. Substitutions are a powerful tool to mold your data into the format you need in order to accomplish tasks you couldn't otherwise. If you're ever stumped and in need of a workaround, the SUBSTITUTE() function often provides the starting point you need to work out a clever solution.
Apply line breaks to items in a rollup field
When you use a rollup field to aggregate information from multiple fields, it may not always appear in the format you desire. You can fix this with a simple substitution formula but, first, make sure your rollup field uses the following function to create a comma-separated list:
ARRAYJOIN(values)
With that in place, create a new formula field and insert a formula like this one:
SUBSTITUTE({Rollup Field},",","\n")
This works just like any basic substitution but takes the comma that separates each value in your {Rollup Field} (whatever you named it contained in {curly braces}) and replaces it with \n. To a computer, \n means "new line" and so this formula will put each of the combined values in a rollup field on its own line and remove the commas to provide a more human-readable format.
Count the items in a multiple select or multiple collaborator field
Sometimes you need to find out how many collaborators are assigned to a given record or find out how many options exist in a multiple select field. To do that, you need to start with a substitution. Here's a formula that'll get the job done:
IF( LEN({Collaborator}) = 0, 0, LEN(CONCATENATE(",", {Collaborator})) - LEN(SUBSTITUTE({Collaborator}, ",", "")))
Alternatively, you may use this shorter formula if you prefer:
IF({Collaborator},LEN({Collaborator})-LEN(SUBSTITUTE({Collaborator},",",""))+1)
This formula demonstrates a very creative use of the SUBSTITUTE() function, but first you have to know the input it receives from the {Collaborator} field to understand why. When you have multiple items in a field, Airtable stores them as an array. For our intents and purposes, an array is just a list of each item in the field separated by a comma. For example:
Joe Brown,Talia Martin,Harrison Leanora
The above formulas count the number of characters in a string of text like the one above but also use the SUBSTITUTE() function to count that same string with the commas removed. By subtracting the smaller number from the larger one and adding one, you end up with the total number of collaborators or multiple select options added to the field. That's typically not how you'd expect to utilize substitutions, but the fact that you can is what makes this function so powerfully versatile.
Count the number of unique linked records
If the above function sounds useful but you want to count only the number of unique values, you need a somewhat different formula. This example counts the number of unique values in a rollup field:
IF( LEN(ARRAYJOIN(ARRAYUNIQUE({Rollup Field}))) = 0, 0, LEN(CONCATENATE(",", ARRAYJOIN(ARRAYUNIQUE({Rollup Field})))) - LEN(SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE({Rollup Field})), ",", "")))
You can also create a separate formula field to count unique linked records in the same way if you prefer. Either way, you only need to change instances of {Rollup Field} to the name of the field you want to count and you're good to go.
Check for duplicates
Perhaps you want to check a large text field for duplicate occurrences of a word or phrase. This formula uses a similar method to the previous formula to accomplish that task:
IF(LEN({Description})-LEN(SUBSTITUTE({Description},"Duplicate Text",", "))>LEN("Duplicate Text"),"Yes","No")
The formula checks the {Description} field for instances of "Duplicate Text" and uses SUBSTITUTE() to create a version of the original text without instances of "Duplicate Text" inside. It then compares the length of the newly substituted version with the length of the "Duplicate Text" and returns a "Yes" answer if that length is greater than the original length and a "No" answer if it's not.
You can replace {Description} with any field or text of your choice. Instances of "Duplicate Text" can be replaced by other text or a {Field} of your choice as well.
Find any word based on its position
If you need to get the third word in a sentence, or the 20th word in a paragraph, you can create a formula with the SUBSTITUTE() function that can locate it easily. You simply need to take the following formula and replace WORD_N with the position number of the word you want to locate:
TRIM(MID(SUBSTITUTE({Name}," ",REPT(" ",LEN({Name}))), (WORD_N-1)*LEN({Name})+1, LEN({Name})))
This formula works well for splitting up full names into their separate components. The example references the {Name} field in a record and finds the the word in the position specified. If the {Name} field contained both a first name and last name, you would replace WORD_N with 2 and the formula would return only the last name.
Make your text URL-friendly
This nested substitution formula, created by one of the Airtable community's formula experts W. Vann Hall, nests a ton of substitution functions to make any text URL-friendly:
LOWER( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({name},"%","%25"), " ","%20"), "!","%21"), "#","%23"), "$","%24"), "'","%27"), "(","%28"), ")","%29"), "*","%2A"), "+","%2B"), ",","%2C"), ":","%3A"), ";","%3B"), "=","%3D"), "@","%40"), "[","%5B"), "]","%5D"))
You can use this formula to pre-format URLs within Airtable to save time and prevent potential issues. This works well for creating automatic search queries, calling certain types of public REST APIs, and anything else you might need to insert into a customized URL.
Need more or have a great formula of your own? đź’ˇ
If you've got a great substitution formula, or just a great formula in general, share your knowledge in the Airtable community forums! Alternatively, if you're looking for a formula to perform a specific function but don't know how to create it, you can post there and our friendly forum denizens will help you out.