Extracting text from fields in Airtable

This article covers how to extract specified values from fields to streamline your team's and organization's work.

Quick Reference: Common Text Extraction Use Cases

Before diving into each function, here's a quick reference for choosing the right approach:

Using LEN() in Airtable

This is a very straightforward function that simply returns the length of a string. You can reference a value/field/string within the function following this format: LEN(string). In the example below, this formula would be used: LEN({File})

A popular use for this function is determining word and character counts in order to alert you if you've ran over a specified limit (word counts for blog articles, tweets). Let's say that you have a base setup to track social media content, and want to make sure your tweets don't exceed the 280 character limit. You can use a formula like the following to output the character count of the text field: LEN({Long Text})

Using LEFT() and RIGHT() in Airtable

The LEFT() and RIGHT() functions work similarly to one another by extracting a certain number of characters from the beginning (left) or end (right) of a string.

• LEFT("quick brown fox", 5) would result in "Quick"

• RIGHT("quick brown fox", 3) would result in "Fox"

These are helpful functions when to use when you need to extract data from a number of records that share a similar structure. For example, you may be importing a dataset where a single cell contains both a date string and a text string, like 02-25-2018_In-Progress.

Assuming that the dates follow the same format of DD-MM-YYYY, you can use LEFT() to extract the date from the full field value: LEFT({Field Name}, 10)

Note

Before extracting text with LEFT(), RIGHT(), or MID(), use TRIM() to remove extra whitespace that might affect your results: LEFT(TRIM({Text Field}), 10)

Why this matters: If your field has leading spaces like "  Hello", LEFT({Field}, 5) would extract "  Hel" (with spaces), but LEFT(TRIM({Field}), 5) would extract "Hello".

Using MID() in Airtable

This function lets you extract a certain number of characters starting at a specific place within a string. It follows this format: MID(string, whereToStart, count). Let's use the example field value from the previous section to demonstrate.

For a field that contains this value, 02-25-2018_In-Progress, we might want to only extract the status portion of the string. Since the dates always follow the same format, and the underscore character is always the 11th character in the string, we want to extract any text past that (starting at the 12th character) to obtain the status: MID({Field Name},12 ,30)

The 30 specified at the end is the count of characters I want to extract. This is a number of characters set to account for the different statuses that may be present in the dataset (e.g. Not Started, Complete). You can always set an arbitrarily high number of characters (like 100, 500, 1000, etc) to account for variations in the dataset.

Using REGEX_EXTRACT() and REGEX_REPLACE() in Airtable

For pattern-based text extraction, Airtable provides REGEX functions that can identify and extract text matching specific patterns. Use REGEX functions when:

• You need to extract data that follows a pattern (emails, phone numbers, URLs, etc.)

• The position of the data varies (MID wouldn't work)

• You need to validate format while extracting

Here are some commonly used patterns:

Note

• Test your REGEX patterns on a small sample of records first. REGEX can be complex, and it's easy to match unintended text. Consider using an online REGEX tester to validate your patterns before applying them in Airtable.

• Search the Airtable Community for similar use cases or ask a question with specific examples.

When to Use AI Fields for Text Extraction

While formula functions like LEFT(), RIGHT(), MID(), and REGEX_EXTRACT() work well for structured data and patterns, AI fields provide intelligent text extraction for unstructured data.

Use AI fields instead of formulas when you need to:

1. Extract from Unstructured Text

   1. Scenario: Extract action items from meeting notes

   2. Formula approach: Would require complex REGEX and might miss variations

   3. AI approach:

      1. Prompt: "Extract all action items from {Meeting Notes} as a bulleted list"

      2. Example input: Met with Sarah about Q1 planning. We decided to increase budget by 15%. John needs to prepare the presentation by Friday. Sarah will reach out

         to the design team about mockups. Follow up next Monday.

      3. AI output:
         • John: Prepare presentation by Friday

         • Sarah: Reach out to design team about mockups

         • Team: Follow up meeting next Monday

2. Extract Multiple Related Items

   1. Scenario: Extract key information from customer feedback

   2. AI Prompt: From {Feedback}, extract: 1) Main issue, 2) Product mentioned, 3) Sentiment, 4) Priority level

   3. Example Input: The mobile app keeps crashing when I try to upload photos. This is really

      frustrating because I need this for work daily. Please fix ASAP!

   4. AI Output:

      1) Main issue: App crashes during photo upload

      2) Product: Mobile app

      3) Sentiment: Frustrated/Negative

      4) Priority: High (ASAP request, impacts daily work)

3. Intelligent Entity Extraction

   1. Formula approach: Would need to know all possible company names in advance

   2. AI prompt: "Identify and extract any company names mentioned in {Email Content}"

4. Contextual Extraction

   1. Scenario: Extract due dates in various formats

   2. Formula approach: Would need separate formulas for each date format

   3. AI prompt: "Extract the deadline or due date from {Task Description} and format as MM/DD/YYYY"

   4. AI output:

      - "Due by Friday" → converts to actual date

      - "Deadline: 12/15/2024" → 12/15/2024

      - "Need this by end of month" → calculates date

      - "ASAP" → identifies as immediate

Note

AI fields require an Airtable workspace plan that includes AI features. Check Airtable AI billing for pricing and availability.

Using arrays in Airtable

When working with rollup or lookup fields, the value that is returned is formatted as an array. An array can contain a list of values, rather than a single value, and is treated differently than a plain string of text (even if that string had multiple values).

When using arrays with any of the functions covered in this article, you'll run into an error if you just try to reference the array.

You'll need to join the items in the array into a string in order to use these functions. The adjusted formula LEN(ARRAYJOIN({Lookup Task Code})) will convert the arrayed results on the lookup into a readable string, and then return the length of that string.

FAQs