Excel Practice
Lessons Lesson 59

LEFT with FIND: Splitting a Name

About this lesson

Putting FIND inside LEFT splits text at a character whose position you cannot predict. So one formula takes the first name out of every row, whatever the names are.

By the end you can

  • split text at a character by putting FIND inside LEFT.
  • take the part after a character with MID and FIND together.

Practises LEFT FIND MID LEN

The idea

This is the first formula in the course where one function's answer becomes another's argument. It deserves a pause. LEFT needs to be told how many characters to take. You do not know, because the names are different lengths. FIND works it out. Put it where the number goes, and the formula adjusts itself.

The minus one is where people get hurt. Learn it as a sentence, not a habit. FIND returns where the space is. LEFT wants how many characters come before it. Those differ by one. Leave the minus one out and you get the name with a space stuck on the end. It looks completely correct on screen, because the space is at the end and nothing moves. Then it fails to match anything. A VLOOKUP against it returns #N/A, and a comparison returns FALSE. You will check the lookup, the range and the data before you check the length. =LEN() on the result is the two-second answer. The vlookup-errors lesson has the other half of this story.

The mistake to watch for

Leaving out the minus one. FIND returns where the space is. LEFT wants how many characters come before it. Those differ by one. Without the subtraction, the first name comes back with a space on the end. It looks the same on screen, and it fails to match anything it is compared to. LEN on the result is how you catch it.

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

Before combining anything, find where the space is in A2. Put the position in B3 for a moment.

To begin, type it exactly:

=FIND(" ",A2)

B3
Row A B C D E F G
1 Contact First name Surname
2 Amara Osei
3 Tomasz Wójcik
4 Ingrid Halvorsen
5 Dele Adeyemi
6
7
8

Every step

  1. 1

    Before combining anything, find where the space is in A2. Put the position in B3 for a moment. To begin, type it exactly: =FIND(" ",A2).

    Hint. Look for a single space.

  2. 2

    Now the whole thing in one formula. In B2, take the first name out of A2, everything before the space. Put the FIND inside the LEFT.

    Hint. The position of the space is one more than the length of the first name.

  3. 3

    This is where people get a trailing space they cannot see. Read =LEN(LEFT(A2,FIND(" ",A2))), the same formula without the minus one, and say what it returns.

    Hint. Count the characters LEFT actually took.

  4. 4

    Now the other half. In C2, take the surname out of A2, everything after the space.

    Hint. One past the space, and then everything.

  5. 5

    B5 was meant to take the first name out of A5. The result will not match anything it is compared against, even though it looks right. Repair it.

    Hint. One character too many.

  6. 6

    In B4, get the first name out of the Norwegian contact in A4. Any way you like, as long as it uses FIND to locate the space. Do not count the characters yourself.

    Hint. Find the space, then take everything before it.