Extract First, Last and Middle Names in Google Sheets (Formula Options)

If you are an Excel user, you may have already formulas to extract first, last, and middle names in Google Sheets.

Yes! I am talking about the SEARCH function combination with LEFT, RIGHT, LEN, and MID functions. But in Google Sheets, in addition to these, there are even better solutions to achieve the same.

You May Like: Google Sheets Function Guide.

Here I am going to present you three different formula options to extract/separate first name, last name, and middle names in Google Sheets.

We can start with the Excel method that works in Google Sheets too. Then the other Google Sheets specific formulas follow.

Extract First, Middle, and Last Names in Google Sheets – The Excel Way

The middle name is separated from the first and last names by space delimiters. So counting the length to the first and second space character is the key here.

How to count the length or number of characters up to the first space and second space in a text?

Assume cell F1 in my sheet contains the name “Elizabeth Smith Brown” in which “Elizabeth” is the first name, “Smith” is the second name and “Brown” is the last name.

Formula to Count Characters Up to the First Space Character in a String

Here is the formula to find the number of characters before the first space in a string in Google Sheets and Excel. Here the string is “Elizabeth Smith Brown”.

Key Formula 1:

=search(" ",F1)

This would return 10. That means there are 10 characters up to the first space character in the string in cell F1.

To find the number of characters up to the second space in a text string, we can nest two SEARCH formulas.

Formula to Count Characters Up to the Second Space Character in a String

First, see the syntax of the function SEARCH.

SEARCH(search_for, text_to_search, [starting_at])

We can make use of the starting_at optional argument here to find the length up to the second space.

How?

It’s by counting the number of characters up to the (second) space character from the 11th character. Didn’t get?

As you may already know the 10th character is a space. So the 11nth character is the starting_at.

That means we can use the just above formula result plus one, i.e. SEARCH(" ",F1)+1, to replace starting_at as below.

Key Formula 2:

=search(" ",F1,search(" ",F1)+1)

This formula would return 16 that means the 16th character is the second space character.

Let’s see how to use these two key formulas in LEFT, MID, Len, and RIGHT formulas to extract the first name, last name, and middle name in Google Sheets and as well as in Excel.

Formula to Extract the First Name

To extract the first name in Google Sheets, extract 10 characters from the left of the string.

=left(F1,search(" ",F1))

The extracted name may contain white space, i.e. the 10th character. If you are so particular, you can remove that as below.

=left(F1,search(" ",F1)-1)

Formula to Extract the Last Name

To extract the last name in Google Sheets, we need to first find the length (total number of the characters) in the string.

Use the LEN as below which would return 21 as the total number of characters in the string (length of the string).

=len(F1)

The total number of characters up to the second space character is 16. Please refer to the ‘Key Formula 2’.

That means we need 21-16, i.e. 5 (please see the below formula), characters from the right of the string to extract the last name.

=len(F1)-search(" ",F1,search(" ",F1)+1)

To extract the 5 characters from the right, use the RIGHT function as below.

=right(F1,len(F1)-search(" ",F1,search(" ",F1)+1))

Formula to Extract the Middle Name

We will use the MID function here.

MID(string, starting_at, extract_length)

I will explain how.

We have already the length up to the first space character, i.e. 10 (using ‘Key Formula 1’). The number of characters up to the second space character is 16 (‘Key Formula 2’).

That means the number of characters in the middle name, i.e. the extract_length, is 16-10. More precisely use it as 16-1-10 = 5.

Generic Formula:
(Key_Formula_2) - 1 - (Key_Formula_1)
Formula:
=search(" ",F1,search(" ",F1)+1)-1-search(" ",F1)

What is the role of -1 in the formula?

Please note that the count up to the second space (16) includes two space characters. By deducting 10, which includes one space, one space character got adjusted. Then in the rest of the 6 characters in the middle name, one character is space. I hope you have got it right.

The 10th character is a space character (‘Key Formula 1’). That means, we need to extract 5 characters from the 11th (‘Key Formula 1+1’) character to extract the mid name in Google Sheets.

So the formula to extract mid name will be;

=mid(F1,search(" ",F1)+1,search(" ",F1,search(" ",F1)+1)-1-search(" ",F1))

Extract First, Last, Middle Names - Google Sheets and Excel

Two Formula Options Unique to Google Sheets

In Google Sheets, there are smarter solutions to separate first, last and middle names. Here are them.

Note: I am unsure whether these solutions are available in the latest iteration of Excel.

Split and Index to Separate First Name, Last Name, and Middle Name in Google Sheets

Using the SPLIT function, we can split the first name, middle name and last name to separate columns in Google Sheets. Then using the INDEX we can effortlessly select the one that we want.

First Name:

=index(split(F1," "),1,1)

Middle Name:

=index(split(F1," "),1,2)

Last Name:

=index(split(F1," "),1,3)

Freakin awesome, right?

REGEXEXTRACT to Extract First, Last, and Middle Names in Google Sheets

Here is yet another awesome solution to separate the first, middle, and last names in Google Sheets.

First Name:

=REGEXEXTRACT(F1&" ","^(\w+\s){1}")

Middle Name:

=REGEXEXTRACT(F1&" ","^(\w+\s){2}")

Last Name:

=REGEXEXTRACT(F1&" ","^(\w+\s){3}")

If you want compatibility with Excel, use the first method, I mean SEARCH, LEFT, RIGHT, MID, and LEN combination. Otherwise, go ahead with either of the REGEX one or the SPLIT-INDEX combo.

That’s all. Enjoy!