Updated: Sep 23
Airtable is a low to no code automation app which works like a spreadsheet, but infinitely better. It can sync in real-time. Airtable is also a database. You can use formulas over the fields to create new fields, which could help you understand your business efficiency better.
In this article, we are going to discover how airtable formulas work. These formulas would be easy for you if you have previous experience of working with languages such as Java, C++, or Python.
There are various kinds of formulas in Airtable. A formula can include a numeric expression, operations, or functions. Some of the formulas are text functions, date and time functions, array functions, regex (regular expression) functions, numeric functions, logical functions, and record functions.
Text functions are those functions that only work with textual data. Airtable text functions make it easy for you to retrieve collective data from the text field.
Let’s go through some of the text formulas.
CONCATENATE() is used to concatenate two or more textual fields together.
For example, Let’s say we have fields called ‘First_name’ and ‘Last_name’.
First_name contains ‘Jane’ and Last_name contains ‘Augsten’.
We can create a new field called ‘Full_name’ with the formula
This will give us the “Jane Augsten”.
It joins the items that are in the array with a separator.
For example, let’s say we have an array called ‘products’ which contains the list of all products Products = (“Soap”,”Brush”,”Shampoo”).
We use the formula ARRAYJOIN(products,”-”). It will give us “Soap-Brush-Shampoo”
It will help us in finding the starting position of the mentioned string.
Example - FIND(“you”,”how are you”) will give an output of “9” because ‘you’ begins with the 9th character.
This formula will replace text.
It starts replacing from the ind_start which is the start of the index from where you want to start replacing.
For example, REPLACE(“We made profit”,9,6,”loss”) will output “We made loss”. ‘Profit’ is replaced with ‘loss’.
There are many other text formulas such as LEFT, RIGHT, REPT, MID, LEN, all of which you can find here. You can use these airtable formulas for your business processes. For example, if you want to send an email with the full name instead of only the first name, you can concatenate first and last names to get the full name.
Array functions are functions that can only be used with array data.
Here are some of the array functions in airtable.
This returns only unique values.
For example, an array named ‘Category’ = [“A”, “B”, “C”, “D”, “A”, “C”]
ARRAYUNIQUE(Category) will return [“A”, “B”, “C”], i.e only the unique values.
This removes the empty strings and null values from the array.
For example, ‘Sale_Complete’ = [“True”, “False”, “Null” , , “ “]
ARRAYCOMPACT(SALE_COMPLETE) will return [“True”, “False”, “ ”]
Note, the string with blank characters is not removed.
This function removes any nesting from the array.