---
title: "Extracting Text From Fields In Airtable | Airtable Support"
slug: "extracting-text-from-a-field"
description: "This article covers how to extract specified values from fields using various methods that Airtable offers."
tags: ["LEFT()", "LEN()", "MID()", "Right()"]
updated: 2026-02-04T19:40:45Z
published: 2026-02-04T19:40:45Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.airtable.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Extracting text from fields in Airtable

| **Plan availability** | All plan types |
| --- | --- |
| **Permissions** | - **Owner** /**Creator** - Have access to all field configuration options. Additionally everything editors, commenters, or read-only users can do. - **Editor** - Can sort by, filter by, group by, or hide the field within a view. - **Commenter** / **Read-only** - Copy a field's URL |
| **Platform(s)** | Web/Browser, Mac app, and Windows app |

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:

| I want to... | Best approach | Function | Example use case |
| --- | --- | --- | --- |
| Count characters | Formula | `LEN()` | Tweet character limits (280 chars) |
| Count words | Formula | `LEN()` + `SUBSTITUTE()` | Blog article word counts |
| Extract first N characters | Formula | `LEFT()` | Get date from "2024-01-15_Status" |
| Extract last N characters | Formula | `RIGHT()` | Extract file extension (.pdf, .docx) |
| Extract from middle | Formula | `MID()` | Get status from "Date_Status_ID" |
| Extract by pattern | Formula | `REGEX_EXTRACT()` | Extract emails, phone numbers, URLs |
| Clean extra spaces | Formula | `TRIM()` | Remove leading/trailing whitespace |
| Extract intelligently | AI field | AI Text field agent | Extract action items from meeting notes |
| Analyze or summarize | AI field | AI Text field agent | Categorize, sentiment analysis |

## 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})`

![360099325994len-1.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360099325994len-1.jpg)

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})`

![360101548533len-2.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360101548533len-2.jpg)

**Displaying a message based on character count**

Now, let's say that you're not so interested in the exact character count, but more so that you're under the 280 character limit. Here's an example of an IF statement you can write to check for this and output a message depending on the character count: `IF(LEN ({Long Text})>280, "❌Over Limit", "✅Under Limit")`

![1500000449682len-3.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/1500000449682len-3.jpg)

Using this formula, you'll see either ❌**Over Limit** or **✅Under Limit** for each record depending on the character count in the field your formula is watching.

![360099326034len-4.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360099326034len-4.jpg)

**Counting words**

For a different workflow, you may be writing editorial content and want to ensure your articles don't exceed a certain word count. You can use a similar formula to count the number of spaces in a text field, which in turn will give you a total count of words: `IF({Text Field}=BLANK(), 0, LEN({Text Field}) - LEN(SUBSTITUTE({Text Field}, ' ', ''))+1)`

![360099326014len-5.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360099326014len-5.jpg)

This formula uses the SUBSTITUTE() function to count spaces, which indicates word count:

1. **SUBSTITUTE({Text Field}, ' ', '')** - Replaces all spaces with empty strings
2. **LEN({Text Field}) - LEN(SUBSTITUTE(...))** - Subtracts the length without spaces from original length, giving you the number of spaces
3. **+1** - Adds 1 because words = spaces + 1 (e.g., "hello world" has 1 space but 2 words)
4. **IF({Text Field}=BLANK(), 0, ...)** - Returns 0 for empty fields instead of 1
5. **About SUBSTITUTE():** The SUBSTITUTE() function replaces one string with another. Format: `SUBSTITUTE(string, old_text, new_text)`. This function is useful for cleaning data or, as shown here, counting occurrences of specific characters.

## 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)`

> [!CAUTION]
> 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.

![1500000450302mid-1.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/1500000450302mid-1.jpg)

## 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:

| Pattern | Matches | Example |
| --- | --- | --- |
| `\\d` | Any digit (0-9) | "5" in "abc5" |
| `\\d+` | One or more digits | "123" in "abc123" |
| `\\w` | Any word character (a-z, A-Z, 0-9, _) | "a" in "a#b" |
| `\\s` | Any whitespace | Space in "hello world" |
| `[A-Z]` | Any uppercase letter | "A" in "Ab" |
| `[a-z]` | Any lowercase letter | "b" in "Ab" |
| `[0-9]{3}` | Exactly 3 digits | "555" in "555-1234" |
| `.` | Any single character | "x" in "xyz" |
| `*` | Zero or more of previous | "aaa" in "aaa" or "" |
| `+` | One or more of previous | "aaa" in "aaa" |

> [!CAUTION]
> 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](https://community.airtable.com) for similar use cases or ask a question with specific examples.

**`REGEX_EXTRACT()` - Extract Matching Patterns**

The REGEX_EXTRACT() function finds and extracts text that matches a regular expression pattern.

**Format:** `REGEX_EXTRACT(text, pattern)`

- ##### Example 1: Extracting email addresses from a text field:
  - `REGEX_EXTRACT({Contact Info}, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}")`
  - Input: "Contact John Doe at john.doe@example.com for details"
  - Output: "john.doe@example.com"
- ##### Example 2: Extracting Phone Numbers
  - Extract US phone numbers in format XXX-XXX-XXXX
  - Input: "Call me at 555-123-4567 tomorrow"
  - Output: "555-123-4567"
- ##### Example 3: Extracting the first number found in text:
  - `REGEX_EXTRACT({Description}, "\\d+")`
  - Input: "Order #12345 received"
  - Output: "12345"
- ##### Example 4: Extracting website URLs from text:
  - `REGEX_EXTRACT({Content}, "https?://[^\\s]+")`
  - Input: "Visit our site at https://airtable.com for more"
  - Output: "https://airtable.com"

**`REGEX_REPLACE()` - Replace Matching Patterns**

The REGEX_REPLACE() function finds text matching a pattern and replaces it.

**Format:** `REGEX_REPLACE(text, pattern, replacement)`

- ##### Example 1: Remove All Numbers
  - `REGEX_REPLACE({Text Field}, "\\d+", "")`
  - Input: "Product ABC123 costs $45"
  - Output: "Product ABC costs $"
- ##### Example 2: Standardize Phone Numbers
  - Removes dashes and parentheses from phone numbers
  - `REGEX_REPLACE({Phone}, "[\\(\\)\\-\\s]", "")`
  - Input: "(555) 123-4567"
  - Output: "5551234567"

## 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

**Combining Formulas and AI Fields**

Sometimes, you may want to use both formula fields and AI together. Here are some high-powered examples:

- Example: Email Processing System

1. **Formula field:** Extract email address with REGEX_EXTRACT()

2. **Formula field:** Count words with LEN() + SUBSTITUTE()

3. **AI field:** Classify email category and extract key points

4. **Formula field:** Create routing logic based on AI classification
- Example: Content Management

1. **AI field:** Extract title and summary from article

2. **Formula field:** Count characters with LEN()

3. **Formula field:** Check if under limit with IF()

4. **AI field:** Generate SEO meta description

> [!CAUTION]
> Note
> 
> AI fields require an Airtable workspace plan that includes AI features. Check [Airtable AI billing](https://support.airtable.com/docs/en/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).

![360099327114wwarray-1.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360099327114wwarray-1.jpg)

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.

![1500000450322wwarray-2.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/1500000450322wwarray-2.jpg)

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.

![360101549373wwarray-3.jpg](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/360101549373wwarray-3.jpg)

## FAQs

**Is there a `SPLIT()` function in Airtable?**

No, Airtable does not have a `SPLIT()` function. However, you have several alternatives depending on your use case:

- Option 1: Use `LEFT()`, `RIGHT()`, `MID()` with `FIND()` for structured data with consistent delimiters:
  - Example: Split "John Doe | [john@example.com](mailto:john@example.com)" at the "|"
    - Extract name (left side): `LEFT({Field}, FIND("|", {Field}) - 1)`
    - Extract email (right side): `TRIM(RIGHT({Field}, LEN({Field}) - FIND("|", {Field})))`
- Option 2: Use `REGEX_EXTRACT()` for pattern-based extraction:
  - Example: Extract email from "Name | Email | Phone"

`REGEX_EXTRACT({Field}, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}")`
- Option 3: Use AI Fields for intelligent extraction from unstructured text:
  - Example: Extract name and email from varied formats
    - AI Prompt: "Extract the person's name and email address from {Contact Info}. Format as 'Name: [name], Email: [email]'"
    - This works even when format varies:
      - "John Doe (john@example.com)"
      - "Contact: john@example.com, Name: John Doe"
      - "john@example.com - John Doe"