Extract First, Last and Middle Names in Google Sheets

In Google Sheets, you can use different formula options to extract first, last, and middle names into separate columns. These methods include using a combination of SEARCH, LEFT, MID, and RIGHT functions, as well as SPLIT with INDEX, and REGEXEXTRACT. Below, you’ll find these options explained so you can choose the one that best fits your needs.

For example, let’s use the following name in cell A2:

Elizabeth Smith Brown

We will apply the formulas in cells B2, C2, and D2. If you have more names in column A, you can drag the formulas down to apply them to the entire column.

Using SEARCH + LEFT/MID/RIGHT Combo

Here, the SEARCH function plays a key role. It helps us find the separator (a space), and based on that, we use LEFT, MID, or RIGHT functions.

Formula to Extract the First Name

To extract the first name, use the following formula:

=IFERROR(LEFT(A2, SEARCH(" ", A2)-1))

The SEARCH function returns the position of the first space character. We then extract the first part of the name using LEFT, stopping one character before the space.

Formula to Extract the Last Name

To extract the last name, use this formula:

=IFERROR(RIGHT(A2, LEN(A2)-SEARCH(" ", A2, SEARCH(" ", A2)+1)))

Here’s how it works:

  • LEN(A2) returns the total number of characters in the name.
  • SEARCH(" ", A2, SEARCH(" ", A2)+1) finds the position of the second space.
  • Subtracting this from LEN(A2) gives us the number of characters in the last name.
  • The RIGHT function extracts that many characters from the right.

Formula to Extract the Middle Name

Use this formula to extract the middle name:

=IFERROR(MID(A2, SEARCH(" ", A2)+1, SEARCH(" ", A2, SEARCH(" ", A2)+1)-SEARCH(" ", A2)-1))

Breaking it down:

  • SEARCH(" ", A2)+1 finds the position of the middle name by locating the first space and moving one character forward.
  • SEARCH(" ", A2, SEARCH(" ", A2)+1)-SEARCH(" ", A2)-1 calculates the length of the middle name.
  • The MID function extracts the middle name based on this position and length.

Using SPLIT and INDEX Combo

The SPLIT function allows us to separate the first, middle, and last names into different columns. Then, the INDEX function helps extract the desired name component.

First Name:

=IFERROR(INDEX(SPLIT(A2, " "), 1, 1))

Middle Name:

=IFERROR(INDEX(SPLIT(A2, " "), 1, 2))

Last Name:

=IFERROR(INDEX(SPLIT(A2, " "), 1, 3))

Awesome, right?

Note: You can replace the INDEX function with the CHOOSECOLS function for a cleaner approach:

=IFERROR(CHOOSECOLS(SPLIT(A2, " "), 1))
=IFERROR(CHOOSECOLS(SPLIT(A2, " "), 2))
=IFERROR(CHOOSECOLS(SPLIT(A2, " "), 3))

Using REGEXEXTRACT

Another powerful method to extract first, last, and middle names in Google Sheets is by using REGEXEXTRACT.

First Name:

=IFERROR(TRIM(REGEXEXTRACT(A2&" ", "^(\w+\s){1}")))

Middle Name:

=IFERROR(TRIM(REGEXEXTRACT(A2&" ", "^(\w+\s){2}")))

Last Name:

=IFERROR(TRIM(REGEXEXTRACT(A2&" ", "^(\w+\s){3}")))

Extract First, Last, and Middle Names – Comparing Formula Results

Sometimes, a name may only have a first name or just a first and last name. Here’s how the formulas behave in such cases:

When There’s Only a First Name:

  • SPLIT-based ✅ Works
  • REGEXEXTRACT-based ✅ Works
  • SEARCH-based ❌ Doesn’t work

When There’s a First and Last Name:

  • SPLIT-based ✅ Works
  • REGEXEXTRACT-based ✅ Works
  • SEARCH-based ✅ Only returns the first name

By using these formulas, you can efficiently extract the first name, last name, and the middle name in Google Sheets with ease.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. With years of experience working with Google Sheets and Excel for multinational firms in Mumbai and Dubai, he has been blogging since 2012, offering practical, real-world spreadsheet solutions that professionals rely on. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.