Split first and last names

Q: How do I split names with middle names in Airtable?

Use LEFT({Full Name}, FIND(" ", {Full Name}) - 1) for the first name. For the last name only (skipping middle names), use REGEX_EXTRACT({Full Name}, "\\s(\\w+)$") to grab the final word. If you want the middle name too, extract the substring between the first and last spaces using a combination of MID(), FIND(), and LEN().

Q: What if some records have only a first name and no last name?

Wrap your formula in a check for spaces: IF(FIND(" ", {Full Name}), LEFT({Full Name}, FIND(" ", {Full Name}) - 1), {Full Name}). This returns the full value as the first name when there is no space. The last name formula should return an empty string in the same scenario.

Q: Is there a way to handle titles like Dr. or Mr. when splitting names?

Use REGEX_REPLACE() to strip titles before splitting: REGEX_REPLACE({Full Name}, "^[A-Z][a-z]+\\.\\s+", ""). This removes patterns like "Dr. " or "Mr. " from the beginning of the string. Then apply the standard first/last name split formulas to the cleaned result.

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

FAQ

How do I split names with middle names in Airtable?

Use LEFT({Full Name}, FIND(" ", {Full Name}) - 1) for the first name. For the last name only (skipping middle names), use REGEX_EXTRACT({Full Name}, "\\s(\\w+)$") to grab the final word. If you want the middle name too, extract the substring between the first and last spaces using a combination of MID(), FIND(), and LEN().

What if some records have only a first name and no last name?

Wrap your formula in a check for spaces: IF(FIND(" ", {Full Name}), LEFT({Full Name}, FIND(" ", {Full Name}) - 1), {Full Name}). This returns the full value as the first name when there is no space. The last name formula should return an empty string in the same scenario.

Can I split names that are in "Last, First" format?

Yes. Use the comma as your delimiter instead of a space. Last name: LEFT({Full Name}, FIND(",", {Full Name}) - 1). First name: TRIM(RIGHT({Full Name}, LEN({Full Name}) - FIND(",", {Full Name}))). The TRIM() removes the space after the comma.

Is there a way to handle titles like Dr. or Mr. when splitting names?

Use REGEX_REPLACE() to strip titles before splitting: REGEX_REPLACE({Full Name}, "^[A-Z][a-z]+\\.\\s+", ""). This removes patterns like "Dr. " or "Mr. " from the beginning of the string. Then apply the standard first/last name split formulas to the cleaned result.

Build Better Airtable Forms with Filla

Name-splitting formulas exist because data arrived in one field when it should have been two. Filla lets you collect first and last names in separate form fields from the start, so your Airtable records are clean on arrival.

Separate first name and last name fields on every form
Required field validation so no submissions arrive incomplete
Conditional logic to show additional name fields (title, suffix) when needed
Direct sync to your Airtable base with no copy-paste or CSV imports

Start free and connect your first form to any Airtable base.

Create Your First Form Free

References

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

Split First and Last Names in Airtable (3 Formulas)

Split first and last names

Basic split (two-word names)

How it works

Handle names with middle names

Extract only the last word (true last name)

Handle names with titles

Using regex for cleaner formulas

Extract first name (first word)

Extract last name (last word)

Extract first name (skip titles)

Handle names with suffixes

Handle single-word names

Trim whitespace

Common patterns

Simple two-word split

Extract last word only

Handle empty fields

Tips and best practices

Always check for empty fields

Use TRIM for cleanup

Test with edge cases

Consider your data structure

When formulas aren't enough

Quick reference

FAQ

How do I split names with middle names in Airtable?

What if some records have only a first name and no last name?

Can I split names that are in "Last, First" format?

Is there a way to handle titles like Dr. or Mr. when splitting names?

Build Better Airtable Forms with Filla

References