Split strings into separate fields

Split text by a delimiter, extract parts, or parse structured data. This guide covers common string-splitting scenarios in Airtable.

Basic split by delimiter

Split by forward slash

If you have text like "This is the first part / This is the second part" and want to split it:

Before field (everything before the "/"):

LEFT({Text}, FIND("/", {Text}) - 2)

After field (everything after the "/"):

RIGHT({Text}, LEN({Text}) - FIND("/", {Text}) - 1)

Combined on separate lines:

LEFT({Text}, FIND("/", {Text}) - 2) & "\n" &
RIGHT({Text}, LEN({Text}) - FIND("/", {Text}) - 1)

How it works:

FIND("/", {Text}) locates the "/" position
LEFT() extracts characters from the start up to (position - 2) to remove the "/" and space
RIGHT() extracts from the end, calculating start position after the "/"

Split by first number (date parsing)

If you have "Angola 8 April 1994" and want to separate country from date:

Location field (country name):

TRIM(SUBSTITUTE({Original}, DATETIME_FORMAT({Date}, "D MMMM YYYY"), ""))

Date field:

DATETIME_PARSE({Original}, "D MMMM YYYY")

This works because:

DATETIME_PARSE() extracts the date from the string
DATETIME_FORMAT() converts it back to the same format
SUBSTITUTE() removes that formatted date from the original string
TRIM() removes extra spaces

Extract numbers from parentheses

If you have "FICHAR (7), INTERESANTE (11), SEGUIR EVOLUCION (8)" and want to extract just the numbers:

Use regular expressions with REGEX_EXTRACT():

REGEX_EXTRACT({Text}, "\\((\\d+)\\)")

This extracts the first number in parentheses. For multiple numbers, you'd need to use a script or external tool since Airtable formulas can't easily iterate.

Alternative: Extract all numbers (if you want to sum them):

You'd need to use a script for this, as formulas can't easily extract and sum multiple numbers from a string.

Split full names into first and last name

First name extraction

Method 1: Using FIND and LEFT (assumes single space separates first and last):

IF({Full Name}, LEFT({Full Name}, FIND(" ", {Full Name}) - 1))

Method 2: Using REGEX_EXTRACT (more flexible):

IF({Full Name}, TRIM(REGEX_EXTRACT({Full Name}, ".*?\\s")))

Last name extraction

Method 1: Using REGEX_REPLACE (removes everything except last name):

IF({Full Name}, REGEX_REPLACE({Full Name}, ".+\\s", ""))

Method 2: Using REGEX_EXTRACT (extracts last word):

IF({Full Name}, REGEX_EXTRACT({Full Name}, "\\s(\\w+)$"))

Note: These assume the last name is a single word. For multiple-word last names, you'd need more complex regex.

Extract values between keywords

If you have structured text like:

_Date_ Wed, 31 Mar 2021 11:45:50 PDT _Build Version_ 1.1.22 _User ID_ ABC Issue Reported* this is for testing _Actions Tried_ -------------Networking-----

Use REGEX_EXTRACT() with non-capturing groups:

Extract date

IF({String}, TRIM(REGEX_EXTRACT({String}, "(?:Date\\s)(.*)(?:\\sBuild.*)")))

Extract User ID

IF({String}, TRIM(REGEX_EXTRACT({String}, "(?:.*User ID)(.*)(?:\\sIssue Reported.*)")))

Extract Issue Reported

IF({String}, TRIM(REGEX_EXTRACT({String}, "(?:.*Issue Reported)(.*)(?:\\sActions Tried.*)")))

How regex groups work:

(?:...) is a non-capturing group—it matches but doesn't return the match
(.*) is a capturing group—it returns the matched content
TRIM() removes leading/trailing whitespace

These work whether the text is on one line or split across multiple lines.

Split by multiple delimiters

If you need to split by different delimiters, use nested IF() statements:

IF(
  FIND("/", {Text}),
  LEFT({Text}, FIND("/", {Text}) - 2),
  IF(
    FIND("|", {Text}),
    LEFT({Text}, FIND("|", {Text}) - 2),
    {Text}
  )
)

Or use REGEX_EXTRACT() for more complex patterns.

Split long text into separate records

If you have a long text field with multiple lines and want each line in a separate record:

This cannot be done with formulas alone. You'll need:

Script: Use Airtable's scripting extension to split the text by newlines and create multiple records
External tool: Use Make, Zapier, or another automation tool to split and create records
Manual: Copy/paste into a spreadsheet, split there, then import

Common patterns

Extract text before first space

LEFT({Text}, FIND(" ", {Text}) - 1)

Extract text after last space

RIGHT({Text}, LEN({Text}) - FIND(" ", REVERSE({Text})))

Extract text between two delimiters

MID(
  {Text},
  FIND("start", {Text}) + LEN("start"),
  FIND("end", {Text}) - FIND("start", {Text}) - LEN("start")
)

Remove everything after a delimiter

LEFT({Text}, FIND("/", {Text}) - 2)

Remove everything before a delimiter

RIGHT({Text}, LEN({Text}) - FIND("/", {Text}) - 1)

Tips and best practices

Handle missing delimiters

Always check if the delimiter exists before splitting:

IF(
  FIND("/", {Text}),
  LEFT({Text}, FIND("/", {Text}) - 2),
  {Text}
)

Trim whitespace

Use TRIM() to clean up extracted values:

TRIM(LEFT({Text}, FIND("/", {Text}) - 2))

Case-insensitive matching

For regex, use case-insensitive flags or convert to lowercase:

REGEX_EXTRACT(LOWER({Text}), "(?:date\\s)(.*)")

Handle edge cases

Empty strings: Use IF() to check for blank values
Multiple delimiters: Decide which one to use (first, last, or specific)
No delimiter found: Provide a fallback value

When to use formulas vs scripts

Use formulas when:

You need to split into a fixed number of fields (2-3)
The pattern is consistent and predictable
You want real-time updates as data changes
The split is simple (single delimiter, fixed position)

Use scripts when:

You need to split into multiple records
The pattern is complex or variable
You need to iterate over multiple matches
Performance is critical for large datasets

References

Community discussion with solutions for various string splitting scenarios: Formula to break a string of text onto 2 separate strings of text

Airtable: Split Strings of Text into Separate Fields (Complete Guide)