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.

Table of Contents

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.