One of Airtable’s most powerful built-in tools is the Formula Field. With it, you can automatically generate information from your tables without needing to enter it in individually. Check out Airtable’s documentation on the Formula Field here.
While the functions are pretty simple at their core, the real power of the Formula Field comes when you start combining functions together.
Table of Contents
Logical Functions
IF(), AND(), and OR()
IF() is a conditional statement that takes 3 arguments: A logical condition, a value if true, and (optional) value if false. If the 3rd argument is blank, then a False statement will return a blank field (nothing).
IF([logical condition], [value if true], [value if false])
AND() and OR() can be used with IF() to make more complex conditional formulas. AND() takes multiple arguments, and returns true only if all arguments are true.
IF(AND(condition1, condition2), "True", "False")
OR() is similar, but returns true is ANY of the arguments are true.
IF(OR(condition1, condition2), "True", "False")
In this example, the formula for ‘Available?’ will return “In Stock” only when a price is set AND the inventory is higher than 1. Otherwise it returns “Unavailable”
Comparing values
Airtable uses the traditional comparison operators , like > to compare two values. A simple example might be IF(A>B, “true”, “false”). This IF() statement will check if A is greater than B, and output the string “true” if it is, and “false” if not.
The commonly used operators here are:
>(Greater than), < (Less than), = (Equals), != (Does not equal), >= (Greater than or equal to), <= (Less than or equal to)
Text Operators and Functions
When dealing with text strings, you can’t simply add and subtract like normal math operations. Luckily there are lots of functions and operators you can use to work with and manipulate text.
Combine multiple text strings
A common use for formulas would be to combine a {First Name} and {Last Name} field into {Full Name} without entering it manually.
You can combine text strings using the & operator:
{First Name} & " " & {Last Name}
Another way to accomplish this is with CONCATENATE(), like this:
CONCATENATE( {First Name}, " ", {Last Name})
Both formulas give the same result:
Word Count and Character Count
Say you want to count the number of characters in a text string. That can be achieved easily with LEN():
LEN({Text Field})
What if you want to count the number of words though? That requires a few extra tricks, but the formula ends up looking like this:
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Text Field},"\n"," "),"__"," "),"__"," ")) - LEN(SUBSTITUTE(SUBSTITUTE({Text Field}," ",""),"\n","")) + 1
Search for phrase within a string
Sometimes you want to search for a specific phrase within a string, and figure out where that phrase appears (if at all).
Airtable has functions called FIND() and SEARCH(), which are very similar and only differ in how they react if the phrase is not found within your search string. FIND() will return 0 in this case, while SEARCH() will return empty.
Let’s say we want to find the phrase “rabbit” in a table of strings.
FIND("rabbit", {String Name})l
SEARCH("rabbit", {String Name})
The formula outputs the character number where the phrase first begins. As you can see, if the phrase is not found, then FIND() will return 0 while SEARCH() will return nothing.
Slice a substring from a text string from the left or right
Using the LEFT() and Right() functions, you can extract part of a text string from the start or the end. Both functions take 3 arguments: What to search for, Where to search for it, and the starting position (optional).
By adding SEARCH() to these functions, we can separate important information from a text string.
Say there is a text field called {Order ID}, that includes both a order number, and the item details. We want to separate these 2 pieces of information into separate fields.
To get the Order #, we can use this formula: LEFT({Order ID}, SEARCH("-", {Order ID}) - 1)
To retrieve the item name, we need to know the length of the full string, then subtract the location of “-“. Note that this would only work if the character “-” never appears in the item names.
IF(SEARCH("-", {Order ID}), RIGHT({Order ID}, LEN( {Order ID}) -SEARCH(" - ", {Order ID}) - 2))
Miscellaneous Formulas
Generate a URL to individual records
Each record is given a unique Record ID that you can retrieve in formaulas using Record_ID(). You could use this to generate a link directly to a record. Note: The person clicking the link needs to have access to the table in question.
First, right-click on any Record and select “Copy record URL”. Paste this somewhere where you can see the format. The simple format is https://airtable.com/[Base ID]/[Table ID]/[Record ID]. You need to remove the [Record ID] from the url, and build a formula that looks something like:
"https://airtable.com/[Base ID]/[Table ID]/" & RECORD_ID()
