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 Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

Reset SCAN by Another Column in Google Sheets and Excel

Resetting SCAN function results based on values in another column is a topic of...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

More like this

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

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.