Split First and Last name

This tutorial covers how to split first and last names in excel.

Splitting First and Last names from a combined cell in Excel is very easy. The following formula is useful when you have one cell containing text which needs to be split up. One of the most common examples of this is when a person’s Forename and Surname are entered in full into a cell. The formula uses various text functions to accomplish the task. The technique uses the space between the names to identify where to split. It is assumed that the name to be split is in Cell B3.

To follow along, download the attached worksheet.

Initial Split First and Last Names Syntax:

Finding the “First Name”

=LEFT(B3,FIND(" ",B3,1))

Finding the “Last Name OR Surname”

=RIGHT(B5,LEN(B5 )-FIND(" ", B5))

Finding the “Last Name OR Surname” when a Middle name is present.

The formula above cannot handle any more than two names. If there is a middle name also, the last name formula will not show the correct results. To solve the problem you have to use a much longer formula.

=RIGHT(B7,LEN(B7)-FIND("#",SUBSTITUTE(B7," ","#",LEN(B7)-LEN(SUBSTITUTE(B7," ","")))))

Finding the “Middle Name”

=LEFT(RIGHT(B10,LEN(B10)-FIND(" ",B10,1)),FIND(" ",RIGHT(B10,LEN(B10)-FIND(" ",B10,1)),1))
Split First and Last Name

Leave a Reply

SUMIF Function

The SUMIF function in Excel sums up particular cells based upon certain criteria. Criteria is specified using logical operators. Common

Read More »

INT Function

The INT function in Excel rounds a number down to the nearest integer. This function will always round down. For

Read More »
Scroll to Top