‹Blog

Airtable Formula: Split First and Last Names

FE
Filla Editorial•beginner•Nov 21, 2025

Split first and last names

You have a "Full Name" field and need to separate it into "First Name" and "Last Name" fields. Here are formulas for different scenarios.

Basic split (two-word names)

For simple names like "John Smith" or "Jane Doe":

First name:`

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

Last name:

IF({Full Name}, RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})))

How it works

  • FIND(" ", {Full Name}) locates the position of the first space
  • LEFT() extracts everything before the space (minus 1 to exclude the space)
  • RIGHT() extracts everything after the first space
  • IF() checks that the field isn't empty before processing

Handle names with middle names

If you have names like "John Michael Smith" and want everything after the first space as the last name:

First name (same as above):

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

Last name (includes middle name):

IF({Full Name}, TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name}))))

This will give you:

  • First name: "John"
  • Last name: "Michael Smith"

Extract only the last word (true last name)

If you want just the final word as the last name (e.g., "Smith" from "John Michael Smith"):

First name:

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

Last name (using regex):

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

Or without regex (more complex):

IF(
  {Full Name},
  RIGHT(
    {Full Name},
    LEN({Full Name}) - FIND(" ", REVERSE({Full Name}))
  )
)

Handle names with titles

If your names include titles like "Dr. John Smith" or "Mr. Jane Doe":

First name (skips title):

IF(
  {Full Name},
  IF(
    FIND(". ", {Full Name}),
    TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
    {Full Name}
  )
)

Then split the remaining name:

First name (after removing title):

IF(
  {Full Name},
  LEFT(
    IF(
      FIND(". ", {Full Name}),
      TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
      {Full Name}
    ),
    FIND(" ", IF(
      FIND(". ", {Full Name}),
      TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
      {Full Name}
    )) - 1
  )
)

Last name (after removing title):

IF(
  {Full Name},
  TRIM(RIGHT(
    IF(
      FIND(". ", {Full Name}),
      TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
      {Full Name}
    ),
    LEN(IF(
      FIND(". ", {Full Name}),
      TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
      {Full Name}
    )) - FIND(" ", IF(
      FIND(". ", {Full Name}),
      TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(". ", {Full Name}) - 1)),
      {Full Name}
    ))
  ))
)

Tip: For cleaner formulas with titles, consider using helper fields or regex.

Using regex for cleaner formulas

Regex can make name splitting more robust:

Extract first name (first word)

IF({Full Name}, REGEX_EXTRACT({Full Name}, "^([^\\s]+)"))

Extract last name (last word)

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

Extract first name (skip titles)

IF(
  {Full Name},
  REGEX_EXTRACT(
    REGEX_REPLACE({Full Name}, "^[A-Z][a-z]+\\.\\s+", ""),
    "^([^\\s]+)"
  )
)

This removes titles like "Dr.", "Mr.", "Mrs." before extracting the first name.

Handle names with suffixes

For names with suffixes like "John Smith Jr." or "Jane Doe III":

First name:

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

Last name (removes common suffixes):

IF(
  {Full Name},
  TRIM(
    REGEX_REPLACE(
      REGEX_REPLACE(
        REGEX_REPLACE(
          REGEX_REPLACE(
            RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})),
            "\\s+Jr\\.?$", ""
          ),
          "\\s+Sr\\.?$", ""
        ),
        "\\s+III$", ""
      ),
      "\\s+II$", ""
    )
  )
)

Or extract just the last name part (before suffix):

IF(
  {Full Name},
  REGEX_EXTRACT(
    RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})),
    "^([^\\s]+)"
  )
)

Handle single-word names

If some records might only have one word (like "Madonna" or "Cher"):

First name:

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

Last name:

IF(
  {Full Name},
  IF(
    FIND(" ", {Full Name}),
    TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name}))),
    ""
  )
)

This ensures:

  • If there's a space, split normally
  • If no space, first name gets the whole name, last name is empty

Trim whitespace

Always use TRIM() to handle inconsistent spacing:

First name:

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

Last name:

IF({Full Name}, TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name}))))

Common patterns

Simple two-word split

First: LEFT({Full Name}, FIND(" ", {Full Name}) - 1)
Last:  RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name}))

Extract last word only

First: LEFT({Full Name}, FIND(" ", {Full Name}) - 1)
Last:  REGEX_EXTRACT({Full Name}, "\\s(\\w+)$")

Handle empty fields

First: IF({Full Name}, LEFT({Full Name}, FIND(" ", {Full Name}) - 1))
Last:  IF({Full Name}, RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})))

Tips and best practices

Always check for empty fields

Wrap formulas in IF({Full Name}, ...) to avoid errors on empty records.

Use TRIM for cleanup

Always wrap extracted values in TRIM() to handle leading/trailing spaces:

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

Test with edge cases

Before deploying, test with:

  • Single-word names
  • Names with multiple spaces
  • Names with titles (Dr., Mr., Mrs.)
  • Names with suffixes (Jr., Sr., II, III)
  • Empty or blank fields
  • Names with special characters

Consider your data structure

  • Simple names: Use basic LEFT() and RIGHT() formulas
  • Complex names: Use regex with REGEX_EXTRACT() and REGEX_REPLACE()
  • Variable formats: Consider using scripts or external tools

When formulas aren't enough

Formulas work great for consistent name formats. For more complex scenarios, consider:

  • Scripts: When you need to handle highly variable name formats or split into multiple records
  • External tools: Make, Zapier, or name parsing APIs for complex name structures
  • Manual review: For critical data, consider a manual review step for edge cases

Quick reference

Scenario First Name Formula Last Name Formula
Simple two-word LEFT({Full Name}, FIND(" ", {Full Name}) - 1) RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name}))
With safety check IF({Full Name}, LEFT({Full Name}, FIND(" ", {Full Name}) - 1)) IF({Full Name}, RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})))
Last word only (regex) LEFT({Full Name}, FIND(" ", {Full Name}) - 1) REGEX_EXTRACT({Full Name}, "\\s(\\w+)$")
Handle single word IF(FIND(" ", {Full Name}), LEFT({Full Name}, FIND(" ", {Full Name}) - 1), {Full Name}) IF(FIND(" ", {Full Name}), RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})), "")
With TRIM TRIM(LEFT({Full Name}, FIND(" ", {Full Name}) - 1)) TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(" ", {Full Name})))

References

Community discussion with solutions for splitting first and last names: Split First and Last Names

Airtable Formula: Split First and Last Names