10 Airtable Formulas All No-coders Should Know (With Examples)

As a highly customizable low-code platform, Airtable is one of those apps that you hear about long before you step into the nocode world. There are many reasons to believe this cloud-hosted, spreadsheet-database hybrid is one of the best for nocode automations, but top of the list is its native integrations with hundreds of apps (including ours!).

Airtable’s modular design allows users to build systems catered specifically to their use case. By adding app extensions, setting up interfaces, and creating automations, you can access information in an easier and more meaningful way. However, not all the data you gather is in its most valuable form.

Enter formulas.

A spreadsheet mainstay, formulas open up a new world of possibility with your data. You can use them to reference other fields, specify conditions, and otherwise leverage the contents of your base in a more meaningful way.

All Airtable formulas have their time in the sun. But if you aren’t planning to become the next database consultant, knowing just a few of the most commonly used items in nocode automations can help you get more value out of your base.

How to Use Airtable Formulas

Formulas are an Airtable field type, so to add one to your table, simply click "+" to add a new field and select Formula from the drop-down list.

There will be an empty input field for you to insert your formula, and you have the option to manually type it out or paste a pre-existing one.

If your output is a number or a date, you can also modify it to best suit your use case by choosing from the following formatting options:

• Number : Decimal, integer, currency, percent, duration
• Date : Date format (local, friendly, US, European, ISO), time format (12 hour, 24 hour), use the same time zone for all collaborators, display time zone

A single formula isn’t always enough to return the value you need, which is why you may use an expression to combine values, fields, and formulas together.

Bear Tip 🐻: When working with complicated formulas and expressions, you might want to test your logic before applying it to your main set of data. Airtable has a Formula Playground base that you can duplicate and play around with for this very purpose!

❗ Note : Formulas in Airtable may seem similar to those used in Excel, but keep in mind that they reference entire fields rather than individual cells. This is in line with the platform’s function as a spreadsheet-database hybrid.

Types of Airtable Formulas

There are dozens of formulas available for use, with each one falling under one of the following categories:

1. Text operators and functions : To manipulate and return text values
2. Logical operators and functions : To compare and return values based on logical expressions
3. Numeric operators and functions : To calculate and return numeric values
4. Date and time functions : To format, interpret, and return date and time values
5. Array functions : To join, remove, or separate values in an array
6. Record functions : To return record data
7. Regex functions : To return values matching a regular expression

Look through Airtable’s Formula Playground or Formula Field Reference page to see a comprehensive list of descriptions, syntax, and examples of the functions in order to decide what best suits your use case.

10 Useful Formulas for Your Nocode Automations

While there are almost a hundred different formulas and countless ways to join them into expressions, it’s unlikely that you’re going to be using them all any time soon. In fact, it’s more likely that you’ll learn a few of the most useful formulas and find that they get the job done—most of the time, that is.

In this article, we’ll go over 10 of the (arguably) most useful Airtable formulas for your nocode automations, complete with technical functions and examples.

1 - LEFT() and RIGHT()

Technical Function: LEFT(string, howMany) and RIGHT(string, howMany)

The LEFT() and RIGHT() formulas extract a certain number of characters from the beginning or end of the string. Because they extract the same number every time, these formulas are best used with consistent datasets, such as invoice numbers or area codes.

Example 1 :

Let’s use LEFT() to extract year of registration and RIGHT() to extract a summarized ID number from a dataset of student IDs.

Using the formula LEFT({Student ID Number}, 4) and RIGHT({Student ID Number}, 6), we are left with the four-digit year of registration and six-digit number that doesn’t include year or placeholder zeroes.

2 - LEN()

Technical Function: LEN(string)

The LEN() formula shows how many characters a string contains. This can be helpful when you have certain length-based output restrictions, such as when posting ad copy or printing on paper of a limited size.

Example 2 :

Let’s use LEN() to indicate which quotes can be published as Tweets.

Using the formula LEN({Full Quote}), we can see how many characters each quote contains.

3 - IF()

Technical Function: IF(expression, ifTrue, ifFalse)

The IF() formula is one of the most useful formulas for nocode automations. Acting based on a logical expression, it returns value1 if the argument is true and value2 if the argument is false. This helps you separate data based on a certain condition without having to manually filter through it.

IF() formulas are often combined with others to create expressions, allowing you to trigger calculations based on whether or not the input meets a condition.

Example 3 :

Let’s use IF() to indicate whether or not our quotes from Example 2 meet can be published as Tweets.

Using the formula IF({Quote Length}<280, "Tweet It!", "It's a No-Go"), we can easily see which of the quotes exceed Twitter’s 280-character limit.

4 - SUBSTITUTE()

Technical Function: SUBSTITUTE(string, old_text, new_text, [index])

The SUBSTITUTE() formula replaces occurrences of old_text with new_text, and it’s incredibly helpful if you have large datasets that need to be cleared of certain keywords or phrases that need to be extracted in a creative way.

Unlike REPLACE(), SUBSTITUTE() replaces all occurrences or a certain number of occurrences of old_text without beginning at a specified start point.

Example 4 :

Let’s use SUBSTITUTE() to extract the date from a string including name and date.

Using the formula SUBSTITUTE(SUBSTITUTE({Reviewer Name + Date}, {Reviewer Name}, ' ')," on ",""), we are left with only the review date.

This formula substitutes the Reviewer Name with a blank space, then does the same with “on”. Because the length of the input string varies, SUBSTITUTE() is a better formula for this use case compared to RIGHT() or something similar.

5 - DATETIME_DIFF()

Technical Function: DATETIME_DIFF([date1], [date2], 'units')

The DATETIME_DIFF() formula calculates the difference between datetimes in specified units (milliseconds, seconds, minutes, hours, days, weeks, months, quarters, years). This can be useful in a wide range of situations, such as calculating subscription length, counting down to an event, or triggering an action when a milestone is reached.

Example 5:

Let’s use DATETIME_DIFF() to calculate membership length.

Using the formula DATETIME_DIFF({Membership End}, {Membership Start}, 'days'), we can determine how long each customer has been a member.

These numbers can be used as insight for strategic decision making or to trigger events, such as a special gift for each year of membership.

6 - CONCATENATE()

Technical Function: CONCATENATE(text1, [text2, ...])

The CONCATENATE() formula joins text arguments together into a single value. Equivalent to the use of the & operator, this function can be used to combine names, create reference codes, and join mailing addresses.

Example 6:

Let’s use CONCATENATE() to create unique order reference codes.

Using the formula CONCATENATE({Branch}, ": ", {Supplier}, " - ", {Order Number}), we are left with concatenated reference codes that summarize not only order number but also associated parties.

7 - ARRAYUNIQUE()

Technical Function: ARRAYUNIQUE([item1, item2, item3])

The ARRAYUNIQUE() formula helps you remove any duplicate items in an array, making your data more digestible. This is helpful when you are aggregating data from long lists and only need unique values, such as the suppliers associated with one client or the team members who have submitted at least one report.

Example 7:

Let’s use ARRAYUNIQUE() to show a list of suppliers that have provided inventory items for one shop location.

Using the formula ARRAYUNIQUE(values) in the aggregate formula section of a rollup field type, we are returned a short list of the suppliers who have sold stock to one shop location, without the duplicates from every single product order.

❗ Note : Array formulas can not be used on text strings, so they will only work when values are first aggregated using a rollup or lookup field.

8 - ROUND()

Technical Function: ROUND(value, precision)

The ROUND() formula rounds a numeric value to the closest number of decimal places to an indicated precision. This helps you avoid long, seemingly endless decimals that make your data look messy. Rounding numeric values is especially helpful when it comes to currencies and actions that require a whole number.

Example 8:

Let’s use ROUND() to approximate project budgets per person to the closest $1.

Using the formula ROUND({Full Budget}/{Team Size}, 1), we are returned a nice, rounded number that can be used for project proposals.

9 - ENCODE_URL_COMPONENT()

Technical Function: ENCODE_URL_COMPONENT(component_string)

The ENCODE_URL_COMPONENT() formula turns a string of text into URL-friendly encoded equivalents. This saves valuable time when constructing browser- and server-readable addresses, especially if you aren’t familiar with URL encoding.

Example 9:

Let’s use ENCODE_URL_COMPONENT() to turn blog titles into URL-friendly snippets and use CONCATENATE() to join the domain and form a full URL.

Using the formula CONCATENATE("https://bannerbear.com/blog/", (ENCODE_URL_COMPONENT({Blog Title})), "/"), we are left with a post URL that is ready to be used as the publishing address.

10 - LAST_MODIFIED_TIME()

Technical Function: LAST_MODIFIED_TIME({field1},{field2},...])

The LAST_MODIFIED_TIME() formula returns the date and time of the most recent modification to a specified field (or fields). This can be helpful when you want to sort data by time but don’t want any later modifications to change the order of your records.

Example 10:

Let’s use LAST_MODIFIED_TIME() to return the time new Tweet data was added to a database.

Using the formula LAST_MODIFIED_TIME({Username}, {Tweet}), we are returned the date and time that a last modification was made, but only to the Username and Tweet fields. Any changes or additions to other fields are not considered, even if they are in the same record.

Nocode Doesn’t Mean No Control

No- and low-code platforms like Airtable are created to minimize the need for technical coding, but they still allow a lot of control using functions and operators. While you do not need to know how to code to use them, understanding the syntax and having an idea of which formula to use in each situation can open up another world of possibilities in your automations.

If you’re looking to learn more about Airtable and how to use it alongside apps like Zapier and Bannerbear, check out a few of our other articles:

👉 All You Need to Know about Airtable’s Rollup Feature (in 2024)

👉 A 5-Minute Quick Guide to Working with Airtable Views

👉 5 Project Management Alternatives to Airtable (2024)