Splitting a Full Name

About this lesson

A full name splits into first and last with TEXTBEFORE and TEXTAFTER at the space. An instance of -1 on TEXTAFTER makes the last name the last word, even when there is a middle name.

By the end you can

split a full name into first and last.
handle a middle name with -1 and build initials with LEFT.

Practises TEXTBEFORE TEXTAFTER LEFT FIND

The idea

The job everybody has to do at least once, and the reason the splitting functions exist. Before them it was LEFT with FIND for the first name, and RIGHT with LEN and FIND for the last. The second of those is a formula most people could not write from memory.

The trap is a middle name. TEXTAFTER at the first space gives Ann Evans. TEXTAFTER at the last space, instance -1, gives Evans. Use -1 always. It does the right thing on two-word names, and it is the only thing that does the right thing on three.

The mistake to watch for

Splitting at the first space. It works on every two-word name and fails on the first person with a middle name. Their last name comes out as Ann Evans. TEXTAFTER with an instance of -1 takes the last word. It costs nothing on two-word names. It is the version to write every time.

Where this comes up again

MID: Taking from the Middle Text: Extracting
LEFT with FIND: Splitting a Name Text: Extracting
Extracting Text: Putting It Together Text: Extracting
LEFT and RIGHT: Taking from the Ends Text: Extracting

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B2, the first name: everything before the space.

To begin, type it exactly:

=TEXTBEFORE(A2," ")

Row	A	B	C	D
1	Full name	First	Last	Initials
2	Ada Lovelace
3	Ben Okri
4	Mary Ann Evans
5	Cai Nguyen
6
7
8

Every step

1

In B2, the first name: everything before the space. To begin, type it exactly: =TEXTBEFORE(A2," ").

Hint. A space between quotation marks.
2

In C2, the last name: everything after the space.

Hint. Same delimiter, other function.
3

Row 4 has three words. Read =TEXTAFTER(A4," ") and say what it returns.

Hint. The first space is after Mary.
4

C4 holds that formula and shows Ann Evans. Fix it so it gives the last word only.

Hint. Last space, so -1.
5

In D2, write a formula using LEFT and TEXTAFTER that gives Ada Lovelace's initials as two letters. That is the first letter of the name, and the first letter of what follows the space.

Hint. Two LEFTs joined by &.