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.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.