Return to Excel Formulas List

Separate First and Last Names – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to separate first and last names in Excel and Google Sheets.

separate first last names Main Function

Separate First Name

First, to separate the first name from the name, we can use the LEFT and SEARCH functions.

=LEFT(B3, SEARCH(" ", B3))

separate first last names 01

Separate Last Name

We then use the SEARCH and MID functions to get the last name from the name.

=MID(B3, SEARCH(" ", B3)+1, 999)

separate first last names 02

We will walkthrough this below.

SEARCH Function (First Name)

First, we used the SEARCH Function to find the position of the space between the first and last names.

=SEARCH(" ", B3)

separate first last names 03

LEFT Function (First Name)

Next, we use the LEFT Function to return the characters before the space (the first name):

=LEFT(B3,C3)

separate first last names 04

Combining these functions yields the original formula for the first name:

=LEFT(B3, SEARCH(" ", B3))

separate first last names 01

Next we will walkthrough how to extract the last name.

In the next section, we will use the SEARCH and MID functions to extract characters from the middle of a text string.

SEARCH Function (Last Name)

First, we used the SEARCH Function to find the position of the space between the first and last names.

=SEARCH(" ", B3)

separate first last names 03

We then add one onto the value returned by this formula to get the starting position of the last name.

separate first last names 05

MID Function (Last Name)

Next we use the MID Function to return all the characters after the space plus 1 (the last name)

=MID(B3, C3+1, 999)

Combining these 2 functions gives us the original formula for the last name.

=MID(B3, SEARCH(B3, " ")+1, 999)

separate first last names 02

Middle Names

Next, we will discuss how to separate names that include middle names or initials.

The calculation for the first name remains unchanged.

To extract a middle name or initial from a name, we would need to use the RIGHT, SEARCH and LEN functions to get the last name from the name string.  We are also going to incorporate the TRIM function to trim any spaces on either side of the text string.

=RIGHT(B4,LEN(B4)-SEARCH(" ",B4)-LEN(TRIM(MID(B4,SEARCH(" ",B4,1)
+1,SEARCH(" ",B4,SEARCH(" ",B4,1)+1)-SEARCH(" ",B4,1))))-1)

separate first last names 06

This formula will only work if there is more than one space in the text string.  If there is only one space, an error with #VALUE would be returned.

To solve this problem, for names without middle names or initials, we can use the original formula using the MID and SEARCH Functions.

We could then combine the 2 formulas using the IFERROR Function.

=IFERROR(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-LEN(TRIM(MID(B3,SEARCH(" ",B3,1)+1,
SEARCH(" ",B3,SEARCH(" ",B3,1)+1)-SEARCH(" ",B3,1))))-1),MID(B3,SEARCH(" ",B3)+1,999))

separate first last names 07

 

We could then use the MID and LEN functions to obtain the middle name or initial.

=MID(B3,LEN(C3)+1,LEN(B3)-LEN(C3&D3))

separate first last names 08

 

Separate First and Last Names in Google Sheets

All the examples explained above work the same in Google sheets as they do in Excel.

separate first last names Google Function