Lessons Lesson 71

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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

A full name splits at the space.

In B2, type:

=TEXTBEFORE(A2," ")

B2
Row A B C D E F G
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. 1

    A full name splits at the space. In B2, type: =TEXTBEFORE(A2," ").

    Hint. A space between quotation marks.

  2. 2

    In C2, take the text after the space with TEXTAFTER. Shape: =TEXTAFTER(cell," ").

    Hint. Use the same space, but TEXTAFTER.

  3. 3

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

    Hint. The first space is after Mary.

  4. 4

    C4 shows Ann Evans, but the last name is just Evans. Fix C4. Remember: an instance of -1 counts from the last occurrence.

    Hint. Last space, so -1.

  5. 5

    In D2, build Ada Lovelace's initials with LEFT and TEXTAFTER. Shape: =LEFT(cell,1)&LEFT(TEXTAFTER(cell," "),1).

    Hint. Two LEFTs joined by &.