Split strings into separate fields

Q: What is the best way to split first and last names in Airtable?

For simple "First Last" names, use LEFT({Full Name}, FIND(" ", {Full Name}) - 1) for the first name and REGEX_REPLACE({Full Name}, ".+\\s", "") for the last name. For names with middle names or prefixes, REGEX_EXTRACT gives more flexibility.

Q: How do I extract text between two keywords in Airtable?

Use REGEX_EXTRACT with non-capturing groups: REGEX_EXTRACT({Text}, "(?:StartKeyword\\s)(.)(?:\\sEndKeyword)"). The capturing group (.) returns only the content between the two keywords.

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

FAQ

Can Airtable formulas split text into separate records?

No. Formulas can only output to a single field on the same record. To split text into multiple records, you need a script (using the Scripting extension or an Automation) or an external tool like Make or Zapier.

What is the best way to split first and last names in Airtable?

For simple "First Last" names, use LEFT({Full Name}, FIND(" ", {Full Name}) - 1) for the first name and REGEX_REPLACE({Full Name}, ".+\\s", "") for the last name. For names with middle names or prefixes, REGEX_EXTRACT gives more flexibility.

Should I use formulas or scripts to split strings?

Use formulas when you are splitting into two or three fixed fields with a consistent delimiter. Use scripts when the number of parts varies, you need to create separate records, or the parsing logic is complex.

How do I extract text between two keywords in Airtable?

Use REGEX_EXTRACT with non-capturing groups: REGEX_EXTRACT({Text}, "(?:StartKeyword\\s)(.*)(?:\\sEndKeyword)"). The capturing group (.*) returns only the content between the two keywords.

Skip the string splitting: collect clean data from the start

Instead of splitting messy form data after the fact, use Filla to collect first name, last name, and other fields separately. Forms write directly to your Airtable base with the structure you need.

Try Filla free →

References

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

Airtable Formula: Split Text into Separate Fields